ALTER PROCEDURE Sel_GoodDateByID @GoodsID int, @retdatetime datetime output AS SELECT @retdatetime = PublishedDateTime FROM LostGoods WHERE GoodsID=@GoodsID RETURN goCREATE PROCEDURE Sel_GoodNum @ID int, @GoodNum int, @OUTPUT int OUTPUT AS DECLARE @GoodDate smalldatetime EXEC Sel_GoodDateByID @ID,@GoodDate output SELECT COUNT(*) FROM LostGoods WHERE PublishedDatetime<=@GoodDate RETURN go
ALTER PROCEDURE Sel_GoodNum @ID int, @GoodNum int, @OUTPUT int OUTPUT AS DECLARE @GoodDate smalldatetime EXEC Sel_GoodDateByID @ID, @GoodDate output /*这里我想把调用子存储过程返回的参数给@GoodDate*/ SELECT COUNT(*) FROM LostGoods WHERE PublishedDatetime<=@GoodDate
RETURN GO ALTER PROCEDURE Sel_GoodDateByID @GoodsID int, @GoodDate smalldatetime output AS SELECT @GoodDate = PublishedDateTime FROM LostGoods WHERE GoodsID=@GoodsID RETURN GO
return 只能返回整数 output可以返回其它类型的数据 如果子过程中返回的是整数 也可以这样: 例create proc usp_number as begin declare @test int select @test=max(number) from master..spt_values where type='p' return @test end declare @t int exec @t=usp_number print @t另外 SET @GoodDate=EXEC Sel_GoodDateByID @ID 这样写是不正确的
@GoodsID int,
@retdatetime datetime output
AS
SELECT @retdatetime = PublishedDateTime FROM LostGoods WHERE GoodsID=@GoodsID
RETURN
goCREATE PROCEDURE Sel_GoodNum
@ID int,
@GoodNum int,
@OUTPUT int OUTPUT
AS
DECLARE @GoodDate smalldatetime
EXEC Sel_GoodDateByID @ID,@GoodDate output
SELECT COUNT(*) FROM LostGoods WHERE PublishedDatetime<=@GoodDate
RETURN
go
@ID int,
@GoodNum int,
@OUTPUT int OUTPUT
AS
DECLARE @GoodDate smalldatetime
EXEC Sel_GoodDateByID @ID, @GoodDate output /*这里我想把调用子存储过程返回的参数给@GoodDate*/
SELECT COUNT(*) FROM LostGoods WHERE PublishedDatetime<=@GoodDate
RETURN
GO
ALTER PROCEDURE Sel_GoodDateByID
@GoodsID int,
@GoodDate smalldatetime output
AS
SELECT @GoodDate = PublishedDateTime FROM LostGoods WHERE GoodsID=@GoodsID
RETURN
GO
请问为什么一定要用输出参数来返回呢??是不是因为返回结果集的结果条数必须到运行时才能确定,所以有一定的不安全性,所以SQL不允许通过结果集返回参数并赋给变量,这样呢??
return 只能返回整数 output可以返回其它类型的数据
如果子过程中返回的是整数 也可以这样:
例create proc usp_number
as
begin
declare @test int
select @test=max(number)
from master..spt_values
where type='p'
return @test
end
declare @t int
exec @t=usp_number
print @t另外 SET @GoodDate=EXEC Sel_GoodDateByID @ID 这样写是不正确的
SET @GoodDate=EXEC Sel_GoodDateByID @ID /*这里我想把调用子存储过程返回的参数给@GoodDate*/请问 @GoodDate smalldatetime 又如何接收一个结果集?