sql server存储过程学习记录

 

declare @name nvarchar(20)

declare @kip nvarchar(20)

if Exists(Select name From sysobjects Where name='oa1_getMaxID' And  type='P')

begin

SET @name='222'

print '@name='+@name

end

else

begin

SET @kip='122'--赋值

Select @name = 

CASE

WHEN @kip = '一' THEN 1

WHEN @kip = '二' THEN 2

WHEN @kip = '三' THEN 3

WHEN @kip = '四' THEN 4

WHEN @kip = '五' THEN 5

ELSE 100

END

print '@name='+@name--输出

end

 

declare @num int, @sqls nvarchar(4000) 

set @sqls=N'select @a=count(*) from dbo.bbsContent ' 

exec sp_executesql @sqls,N'@a int output',@num output 

select @num 

 

CREATE proc SP_proc

as

declare @name nvarchar(20)

declare Mycursor cursor   

for

select name from tb

open Mycursor

fetch next from Mycursor into @name

while @@FETCH_STATUS=0

begin

 if (DATEDIFF(year, getdate(), getdate())>=3

 begin

 

 end

 fetch next from Mycursor into @name

end

close Mycursor       

deallocate Mycursor

 

EXEC SP_proc 'Junn.A'

 

 

SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) --计算一个月第一天

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) --计算一个月最后一天

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) --本周的星期一

select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)--本月的第一个星期一 

SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) --一年的第一天

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))--一年的最后一天

SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) --这个季度的第一天

select datename(weekday,getdate())--显示星期几

select datediff(day,'2004-'+cast(2 as varchar)+'-15' ,'2004-'+cast(2+1 as varchar)+'-15') --计算某个月的天数

select datediff(day,cast(month(GetDate()) as varchar)+'-'+cast(month(GetDate()) as varchar)+'-15' ,cast(month(GetDate()) as varchar)+'-'+cast(month(GetDate())+1 as varchar)+'-15') --计算当月月的天数

SELECT Day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))) --本月的最后一天

SELECT case day(dateadd(mm, 2, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)))) when 28 then '平年' else '闰年' end--判断是否闰年

 

select DATEDIFF(year, '2008-10-11', getdate())--计算相隔年数

select DATEDIFF(month, '2008-10-11', getdate())--计算相隔月数

select DATEDIFF(day, '2008-10-11', getdate())--计算相隔天数

select  DateDiff(dd, getdate() - 1, GetDate())--计算相隔天数

select  DateDiff(wk, getdate() - 1, GetDate())--计算相隔周数

select  DateDiff(week, getdate() - 14, GetDate())--计算相隔周数

select  DateDiff(mm, getdate() - 1, GetDate())--计算相隔月数

select convert(varchar(10),getdate(),120)--今天

Select Convert(Varchar(10), GetDate(), 121)--今天

select convert(varchar(10),getdate() - 1,120)--昨天

select convert(varchar(10),getdate() + 1,120)--明天

select dateadd(day,-1,getdate())--昨天时间

select dateadd(month,-1,getdate())--上月时间

select dateadd(year, -1, getdate())--上年时间

select  month(getdate()) - 1--上月

select month(dateadd(month, -1, getdate())) --上月

select Convert(Datetime,GetDate(),2)