-------創建臨時存儲過程
create proc #pr_xxx
as
select 'fff'
exec #pr_xxx?? ?--執行存儲過程
-------擴展存儲過程
exec xp_cmdshell 'dir c:\'
可執行DOS的命令，如刪除，創建，復制等等
------存儲過程實例一(加法運算)
create procedure pr_sss
@n1 smallint,
@n2 smallint,
@sum smallint output
as
set @sum=@n1+@n2
return 10
go
declare @sss smallint,@ddd int
exec @ddd=pr_sss 20,10,@sss output
print @ddd
print @sss
------存儲過程實例二(pr_helptext)
create procedure pr_helptext?? ?--修改用alter
@arg1 varchar(100)
as
select text from syscomments where object_id(@arg1)=id
go
exec pr_helptext pr_sss
------存儲過程實例三
create procedure pr_ksk
as
insert into ssss_bak
select * from ssss
exec pr_ksk
------自動備份數據庫(with init是重寫)
declare @path varchar(100)
set @path=convert(varchar(10),getdate(),120)
set @path='d:\bak1\'+@path+'.bak'
backup database shidian to disk=@path with init
------自動刪除備份(前5天)
declare @path varchar(100)
set @path=convert(varchar(10),getdate()-5,120)
set @path=' del d:\bak1\'+@path+'.bak'
exec master.dbo.xp_cmdshell @path
-------自動備份刪除文件(前5天)
declare @path varchar(100)
set @path=convert(varchar(10),getdate(),120)
set @path='d:\bak1\'+@path+'.bak'
print @path
backup database shidian to disk=@path with INIT
set @path=convert(varchar(10),getdate()-5,120)
set @path='del d:\bak1\'+@path+'.bak'
print @path
EXEC master.dbo.xp_cmdshell? @path
使用加法運算符將天數加到日期和時間值中??
下面的示例將若干天數加到?? datetime?? 日期上。
SELECT DATEDIFF(mi, CONVERT(datetime,'11:00:00'),CONVERT(datetime,'13:30:00'))/60.0
SELECT DATEDIFF(MINUTE,'2002-1-1 11:00:00','2002-1-1 13:30:00') / 60 + CASE WHEN DATEDIFF(minute,'2002-1-1 11:00:00','2002-1-1 13:30:00') % 60 >=30 THEN 0.5 ELSE 0 END?
???
? DECLARE?? @startdate?? datetime,?? @adddays?? int??
? SET?? @startdate?? =?? '1/10/1900?? 12:00?? AM'??
? SET?? @adddays?? =?? 5??
? SET?? NOCOUNT?? OFF??
? SELECT?? @startdate?? +?? 1.25?? AS?? 'Start?? Date',????
??????? @startdate?? +?? @adddays?? AS?? 'Add?? Date'??
???
? 下面是結果集：??
???
? Start?? Date???????????????????????????????????? Add?? Date??????????????????????????????????????????
? ---------------------------?? ---------------------------????
? Jan?? 11?? 1900???? 6:00AM?????????????????? Jan?? 15?? 1900?? 12:00AM??????????????????