一个存储过程中学到的知识

设计一个存储过程，学到关于数据库的一些知识

IF Exists(Select * From sysobjects Where Name='SP_SumAgileAtt' And xType='P')
  Drop Proc SP_SumAgileAtt
go

Create Proc SP_SumAgileAtt (@Month Varchar(7),@DepIds Varchar(500)='',@EmpNo Varchar(30)='',@UserID Int)
  WITH ENCRYPTION
/*

*/
As
Begin
--SET NOCOUNT ON
Declare @EmpId Int,@i Int,@SelStr Varchar(8000),@BDay Varchar(5),@EDay Varchar(5),@SignOffSet Varchar(5),@BDate Varchar(10),@EDate Varchar(10),@aDate DateTime,@ResName Varchar(60),@FromStr Varchar(8000),@WhereStr Varchar(8000),@AndStr Varchar(200),@ExtStr Varchar(8000)

Create Table #EmpList(EmpID Int)
IF @UserID=1
  Insert #EmpList Select Employee_ID From Employee
ELSE
  Insert #EmpList Select a.Employee_ID From Employee As a,Position As b,Department As c,Users_Permission As d
    Where a.Position_ID=b.Position_ID And b.Department_ID=c.Department_ID And d.Department_Id=c.Department_ID Andd.User_ID=@UserID
Create Table #Attendance  
(iID Int Identity(1,1),
 EmpId Int, 
 dDate datetime,
 Hours Decimal(7,2)
)
Select @SignOffSet=CHARINDEX(',',Option_Value) From Setoption Where Option_ename='calctimezone'
Select @BDay=Substring(Option_Value,1,@SignOffSet-1),@EDay=Substring(Option_Value,@SignOffSet+1,Len(Option_Value)-@SignOffSet) From Setoption Where Option_ename='calctimezone'
IF @BDay>@EDay
  Set @BDate=Convert(Varchar(10),Dateadd(mm,-1,cast(@Month+'-'+@BDay as datetime)),121)
ELSE
  Set @BDate=@Month+'-'+Replicate('0',2-Len(@BDay))+@BDay
IF @EDay<>'31'
  Set @EDate=@Month+'-'+@EDay
ELSE
  Set @EDate=Convert(Varchar(10),DateAdd(d,-1,DateAdd(m,1,@Month+'-01')),121)
 

--Select @BDay,@EDay,@BDate,@EDate
Delete a From #EmpList As a,Demission As b Where a.EmpId=b.Employee_Id And b.Date_Demission<@BDate And Employee_Status='L'
Insert #Attendance(EmpID,dDate,Hours) Select a.Employee_ID,a.dDate,a.Hours
  From Attendance_Agile_Result As a,#EmpList as b Where a.dDate>=@BDate And a.dDate<=@EDateAnd a.Employee_ID=b.EmpID
--- 部门 工号 姓名 上班时数
--  iID,Employee_ID,dDate,Hours,Attendance_Agile_Result
Set @SelStr='select r.EmpID,a.employee_no as [工号],a.name as [姓名],b.department_code as [部门代码],b.name as [部门名称],r.上班时数 as [上班时数]'
Set @FromStr='from employee as a,department as b,position as c'
Set @WhereStr='where a.position_id=c.position_id and b.department_id=c.department_id'

Set @SelStr=@SelStr
Set @FromStr=@FromStr+',(Select EmpID,Sum(Hours) As [上班时数] From #Attendance  Group By EmpID) As r'
Set @WhereStr=@WhereStr+' And a.Employee_Id*=r'+'.EmpID'

-- Print @SelStr
-- Print @FromStr
-- Print @WhereStr

Set @ExtStr=''
IF @DepIds<>''
  Set @ExtStr=' And b.department_id in('+@DepIds+')'
IF @EmpNo<>''
  Set @ExtStr=@ExtStr+' And (a.name like'''+@EmpNo+'%''ora.employee_no='''+@EmpNo+''')'

Exec(@SelStr+''+@FromStr+''+@WhereStr+@ExtStr+'Order By b.Department_Code,a.Employee_No ')

Drop Table #EmpList
Drop Table #Attendance
End  --EndProc
go

--SP_SumAgileAtt '2009-03','','',1
--go

CHARINDEX
　　返回字符串中指定表达式的起始位置。

例一:
　　CustomName包含客户的First Name和Last Name，它们之间被一个空格隔开。我们用CHARINDX函数确定两个名字中间空格的位置。通过这个方法，我们可以分析ContactName列的空格位置，这样可以只显示这个列的last name部分。
　　select top 5 substring(ContactName,charindex(' ',ContactName)+1,len(ContactName)) as [Last Name] from customers
　　CHARINDEX函数找到First Name和Last Name之间的空格，所以SUBSTRING函数可以分开ContactName列，这样就只有Last Name被选出。在CHARINDEX函数返回的整数上加1，这样Last Name不是从空格开始。
　　例二:
　　计算Northwind.dbo.Customer表中Addresses字段中包含单词Road或者它的缩写Rd的记录数，选择语句类似这样：
　　Select count(*) from Northwind.dbo.Customers
　　Where CHARINDEX('Rd',Address) > 0 or CHARINDEX('Road',Address)> 0

--------------------------------------------------------------

DateAdd函数 返回
　　返回包含一个日期的 Variant (Date)，这一日期还加上了一段时间间隔。
　　语法
　　DateAdd(interval, number, date)
　　DateAdd 函数语法中有下列命名参数：
　　部分 描述
　　interval 必要。字符串表达式，是所要加上去的时间间隔。
　　number 必要。数值表达式，是要加上的时间间隔的数目。其数值可以为正数（得到未来的日期），也可以为负数（得到过去的日期）。
　　date 必要。Variant (Date) 或表示日期的文字，这一日期还加上了时间间隔。
　　设置
　　interval 参数具有以下设定值：
　　设置 描述
　　yyyy 年
　　q 季
　　m 月
　　y 一年的日数
　　d 日
　　w 一周的日数
　　ww 周
　　hh 时
　　n 分钟
　　s 秒
　　说明
　　可以使用 DateAdd 函数对日期加上或减去指定的时间间隔。例如，可以用 DateAdd 来计算距今天为三十天的日期；或者计算距现在为 45 分钟的时间。
　　为了对 date 加上“日”，可以使用“一年的日数” (“y”)，“日” (”d”) 或“一周的日数” (”w”)。
　　DateAdd 函数将不返回有效日期。在以下实例中将 1 月31 日加上一个月：
　　DateAdd(m, 1, 31-Jan-95)
　　上例中，DateAdd 返回 1995 年 2 月 28 日，而不是 1995 年 2 月 31 日。如果 date 是 1996 年 1 月 31 日，则由于 1996 年是闰年，返回值是 1996 年 2 月 29 日。

-------------------------

cast 函数 用法2008年04月17日 星期四 16:23示例
A. 同时使用 CAST 和 CONVERT
每个示例都将检索书名（这些图书的截止当前销售额的第一位数字为 3），并将这些图书的 ytd_sales 转换为 char(20)。

-- Use CAST.
USE pubs
GO
SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles
WHERE CAST(ytd_sales AS char(20)) LIKE '3%'
GO

-- Use CONVERT.
USE pubs
GO
SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles
WHERE CONVERT(char(20), ytd_sales) LIKE '3%'
GO

下面是任一查询的结果集：

Title ytd_sales
------------------------------ -----------
Cooking with Computers: Surrep 3876
Computer Phobic AND Non-Phobic 375
Emotional Security: A New Algo 3336
Onions, Leeks, and Garlic: Coo 375

(4 row(s) affected)

B. 使用带有算术运算符的 CAST
下面的示例通过将总的截止当前销售额 (ytd_sales) 与每本图书的价格 (price) 相除，进行单独列计算 (Copies)。在四舍五入到最接近的整数后，此结果将转换为 int 数据类型。

USE pubs
GO
SELECT CAST(ROUND(ytd_sales/price, 0) AS int) AS 'Copies'
FROM titles
GO

下面是结果集：

Copies
------
205
324
6262
205
102
7440
NULL
383
205
NULL
17
187
16
204
418
18
1263
273

(18 row(s) affected)

C. 使用 CAST 进行串联
下面的示例使用 CAST 数据类型转换函数来串联非字符、非二进制表达式。

USE pubs
GO
SELECT 'The price is ' + CAST(price AS varchar(12))
FROM titles
WHERE price > 10.00
GO

下面是结果集：

------------------
The price is 19.99
The price is 11.95
The price is 19.99
The price is 19.99
The price is 22.95
The price is 20.00
The price is 21.59
The price is 10.95
The price is 19.99
The price is 20.95
The price is 11.95
The price is 14.99

(12 row(s) affected)

D. 使用 CAST 获得更多易读文本
下面的示例在选择列表中使用 CAST 将 title 列转换为 char(50) 列，这样结果将更加易读。

USE pubs
GO
SELECT CAST(title AS char(50)), ytd_sales
FROM titles
WHERE type = 'trad_cook'
GO

下面是结果集：

ytd_sales
-------------------------------------------------- ---------
Onions, Leeks, and Garlic: Cooking Secrets of the 375
Fifty Years in Buckingham Palace Kitchens 15096
Sushi, Anyone? 4095

(3 row(s) affected)

E. 使用带有 LIKE 子句的 CAST
下面的示例将 int 列（ytd_sales 列）转换为 char(20) 列，以便使用 LIKE 子句。

USE pubs
GO
SELECT title, ytd_sales
FROM titles
WHERE CAST(ytd_sales AS char(20)) LIKE '15%'
AND type = 'trad_cook'
GO

下面是结果集：

title ytd_sales
------------------------------------------------------------ -----------
Fifty Years in Buckingham Palace Kitchens 15096

(1 row(s) affected)

--------------------------

REPLICATE() --函数返回一个重复character_expression 指定次数的字符串
　　/*select replicate('abc', 3) replicate( 'abc', -2)

-------------------------------------------------