Sql Server自定義存儲過程實例

CREATE      Procedure UserSiteNowDataVIEw_List(
@UserID int, 
@Type int, 
@StartTime varchar(25),
@EndTime varchar(25),
@SiteID int
)
AS 
 
--返回查詢數據
declare @SQLString nvarchar(200)
declare @SQLTemp nvarchar(200)

set @SQLString=N''SELECT * FROM dbo. UserSiteNowDataVIEw WHERE VisitTime between ''''''+@StartTime
set @SQLString=@SQLString+N'''''' and ''''''+@EndTime+''''''''

if @UserID!=0
set @SQLString=@SQLString+N'' and UserID=''+cast(@UserID as nvarchar)

if @SiteID!=0
set @SQLString=@SQLString+N'' and SiteID=''+cast(@SiteID as nvarchar) 

exec sp_executesql @SQLString

--SELECT * 
--FROM dbo. UserSiteNowDataVIEw 
--WHERE VisitTime between @StartTime and DateAdd(d,1,@EndTime) and SiteID=@SiteID Order by id desc

--返回當天數據
/*
if @Type=0 
set @SQLString=n"SELECT * 
FROM dbo. UserSiteNowDataVIEw 
WHERE (CONVERT(varchar(30), VisitTime, 10) = CONVERT(varchar(30), GETDATE(), 10)) "

--返回昨天數據 
if @Type=1 
set @SQLString=n"SELECT * 
FROM dbo. UserSiteNowDataVIEw
WHERE (CONVERT(varchar(30), VisitTime, 10) = CONVERT(varchar(30), DateADD(d,-1,GETDATE()), 10))"
--返回當月數據
if @Type=2 
set @SQLString=n"SELECT * 
FROM dbo. UserSiteNowDataVIEw 
WHERE id is not null"
*/