這樣寫好想不對的阿
OUTPUT 資料指標參數是用來將預存程序的區域資料指標傳回叫用的批次、預存程序或觸發程序。首先,建立在標題資料表宣告並隨後開啟資料指標的程序:USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'titles_cursor' and type = 'P')
DROP PROCEDURE titles_cursor
GO
CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT
AS
SET @titles_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT *
FROM titlesOPEN @titles_cursor
GO接下來,執行一個宣告區域資料指標變數的批次、執行該程序來指派資料指標給區域變數,然後從資料指標提取兩個資料列。USE pubs
GO
DECLARE @MyCursor CURSOR
EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO
OUTPUT 資料指標參數是用來將預存程序的區域資料指標傳回叫用的批次、預存程序或觸發程序。首先,建立在標題資料表宣告並隨後開啟資料指標的程序:USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'titles_cursor' and type = 'P')
DROP PROCEDURE titles_cursor
GO
CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT
AS
SET @titles_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT *
FROM titlesOPEN @titles_cursor
GO接下來,執行一個宣告區域資料指標變數的批次、執行該程序來指派資料指標給區域變數,然後從資料指標提取兩個資料列。USE pubs
GO
DECLARE @MyCursor CURSOR
EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO
使用sp_executesql输出参数
ALTER PROCEDURE dbo.aaa (@tbl varchar(20), @date datetime, @count int output)
AS
Begin
Declare @S Nvarchar(2000)
Set @S='select @count = count(*) from '+@tbl+' where [date] = '+@date+''
Execute sp_executesql @S,'@count int output',@count output
End
GO
(
@tbl varchar(20),
@date datetime,
@count int output
)
AS
declare @sql nvarchar(200)
set @sql=N'select @count= count(*) from '+@tbl+
N' where date = '''+convert(varchar,@date)+''''
exec sp_executesql @sql,N'@count int output',@count output
go
AS
Begin
Declare @S Nvarchar(2000)
Set @S='select @count = count(*) from '+@tbl+' where [date] = '''+@date+''''
Execute sp_executesql @S,'@count int output',@count output
End
GO另外,时间比较最好使用DateDiff。
AS
exec sp_executesql N'select @count = count(*) from @tbl where date = @date',
N'@count int output,@tbl varchar(20),@date datetime',
@count output,@tbl , @date
Insert TEST Values('2005.7.26')
Insert TEST Values('2005.7.27')
Insert TEST Values('2005.7.28')
Insert TEST Values('2005.7.29')
Insert TEST Values('2005.8.1')
Insert TEST Values('2005.8.2')select * from test
where datediff(day,getdate(),日期)>0ALTER PROCEDURE dbo.aaa1 (@tbl varchar(20), @date datetime, @count int output)
AS
Declare @S Nvarchar(2000)
Set @S=N'select @count = count(*) from '+@tbl+' where [date] = @date'
exec sp_executesql @S,
N'@count int output,@tbl varchar(20),@date datetime',
@count output,@tbl,@datedeclare @out int,@date datetime
set @date=cast('2005.7.26' as datetime)
execute dbo.aaa1 'TEST',@date,@out output
select @out
declare @str2 varchar(4000)
declare @tbl varchar(20)
declare @aaa datetime
set @tbl = 'table'
set @aaa = '2005-7-19'
set @str1 = 'select aaa,bbb,ccc,ddd,eee from '+@tbl+''
set @str2 = ' where aaa = '+@aaa+''
exec (@str1 + @str2)
这种方法命令再长都可以执行,只不过这里的日期型变量@aaa该如何在命令里面处理才能正常执行呢?...
exec (str1 + str2 +...)
Create Table TEST(
c1 Varchar(10),
date DateTime)
Insert TEST Values('1', '2005-05-10 00:00:00.000')
Insert TEST Values('101', '2005-05-10 00:00:00.000')
Insert TEST Values('102', '2005-05-11 00:00:00.000')
Insert TEST Values('103', '2005-05-12 00:00:00.000')
Insert TEST Values('2', '2005-05-12 00:00:00.000')
Insert TEST Values('201', '2005-05-15 00:00:00.000')
Insert TEST Values('202', '2005-05-16 00:00:00.000')
GO
Create PROCEDURE dbo.aaa (@tbl varchar(20), @date datetime, @count int output)
AS
Begin
Declare @S Nvarchar(2000)
Set @S='select @count = count(*) from '+@tbl+' where [date] = '''+Rtrim(@date)+''''
Execute sp_executesql @S,N'@count int output',@count output
End
GO
--测试
Declare @count Int
EXEC aaa 'TEST','2005-05-10',@count Output
Select @count
--删除测试环境
Drop Table TEST
Drop PROCEDURE dbo.aaa
--结果
/*
2
*/