Nothing is impossible for a willing heart

[置頂]一些SQL語句

-------創建臨時存儲過程
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??????????????????

posted @ 2006-09-29 22:02 JAVA_UFO 閱讀(252) | 評論 (0) | 編輯 收藏

差不多兩年沒寫blog了

我回來了,哈哈.
       有時間的話,我要把我工作上碰到問題和解決方案都整理出來.

posted @ 2009-01-18 16:54 JAVA_UFO 閱讀(158) | 評論 (0) | 編輯 收藏

一些SQL語句

-------創建臨時存儲過程
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??????????????????

posted @ 2006-09-29 22:02 JAVA_UFO 閱讀(252) | 評論 (0) | 編輯 收藏