在一个存储过程中取得另一存储过程的返回值

[泡泡鱼_]

CREATE PROCEDURE sp_test

(
@Y_A_A_Nums int=0 output
)

AS
DECLARE @Sql nvarchar(1000),@Test int
SET @Sql ='SELECT @Y_A_A_Nums=Y_A_A FROM Init_Week WHERE Member_Code=''QCRX1381'' AND StartDay=''2005-7-31'' AND EndDay=''2005-8-6'''
EXEC sp_executesql @Sql,N'@Y_A_A_Nums int out',@Y_A_A_Nums out
IF @Y_A_A_Nums IS NULL
SET @Y_A_A_Nums=0
SET @Test = 1

我要怎么样才可以在另一个存储过程中取得上面这个存储过程中
@Y_A_A_Nums int
@Test
的值呀？？？？

[net_steven]

How to return a value from dynamicaly executed T-SQL code

Summary

You can return value from a dynamicaly executed T-SQL into a local variable using undocumented in SQL Server Books Online (it's documented in mskb #262499) feature of system extended stored procedure sp_executesql. This method is shorter and simplier than the common practice of using a temporary table.

Description

If you try to return value from dynamicaly executed T-SQL you'll quickly find out that the calling procedure local variables are not within scope of dynamic SQL because it is treated as another batch. The common practice to overcome that limitation involves writing the value to a temporary table and then pulling it out in the calling procedure. Listing #1 illustrates this


-- Listing #1
DECLARE @ret_ext_max nvarchar(100), @SQLstr nvarchar(100)
CREATE TABLE #returnvalue (retval nvarchar(100))
SET @SQLstr = N'SELECT MAX(au_id) FROM pubs..authors'
INSERT INTO #returnvalue EXECUTE (@SQLstr)
SET @ret_ext_max = ( SELECT retval FROM #returnvalue)
PRINT @ret_ext_max
The sp_executesql system stored procedure allows output parameters to be specified so that any output generated from the T-SQL statements can be stored (although this is not documented in SQL Server Books Online). Based on that code above could be rewriten as shown in the Listing #2.


-- Listing #2
DECLARE @ret_ext_max nvarchar(100), @SQLstr nvarchar(100)
SET @SQLstr = N'SELECT @ret_ext_max = MAX(au_id) FROM pubs..authors'
EXEC sp_executesql @SQLSTR, N'@ret_ext_max nvarchar(100) OUTPUT',
@ret_ext_max OUTPUT
PRINT @ret_ext_max
As you can see this code is much shorter and works without temporay table.
The Listing #3 shows how to return more than one value and at the same time pass parameter to the dynamic sql. Notice, that ouput parameters listed first because you have to use only named parameters after the first named parameter has been used.


-- Listing #3
DECLARE @ret_ext_max nvarchar(100), @ret_ext_min nvarchar(100)
DECLARE @SQLstr nvarchar(100)

SET @SQLstr = N'SELECT @ret_ext_min = MIN(au_id), @ret_ext_max = MAX(au_id)' +
' FROM pubs..authors Where State = @State'
EXEC sp_executesql @SQLSTR,
N'@ret_ext_min nvarchar(100) OUTPUT, @ret_ext_max nvarchar(100) OUTPUT, @State char(2)',
@ret_ext_min OUTPUT, @ret_ext_max OUTPUT, @State = 'CA'
PRINT @ret_ext_min
PRINT @ret_ext_max

If for same reason you want to use only named parameter than see Listing #4

-- Listing #4
DECLARE @ret_ext_max nvarchar(100), @ret_ext_min nvarchar(100)
DECLARE @SQLstr nvarchar(100)

SET @SQLstr = N'SELECT @ret_int_min = MIN(au_id), @ret_int_max = MAX(au_id)' +
' FROM pubs..authors Where State = @State'
EXEC sp_executesql @SQLSTR,
N'@State char(2), @ret_int_min nvarchar(100) OUTPUT, @ret_int_max nvarchar(100) OUTPUT',
@ret_int_min=@ret_ext_min OUTPUT, @ret_int_max=@ret_ext_max OUTPUT, @State = 'CA'
PRINT @ret_ext_min
PRINT @ret_ext_max

[vivianfdlpw]

把@test作为output参数或者存储过程返回值

CREATE PROCEDURE sp_test
(
@Y_A_A_Nums int=0 output,
@Test int output
)
AS
DECLARE @Sql nvarchar(1000
SET @Sql ='SELECT @Y_A_A_Nums=Y_A_A FROM Init_Week WHERE Member_Code=''QCRX1381'' AND StartDay=''2005-7-31'' AND EndDay=''2005-8-6'''
EXEC sp_executesql @Sql,N'@Y_A_A_Nums int out',@Y_A_A_Nums out
IF @Y_A_A_Nums IS NULL
SET @Y_A_A_Nums=0
SET @Test = 1
go

然后在另一个过程中：
create procedure p
as
declare @nums int,@test int
exec sp_test @nums out,@test out
select @nums,@test

[泡泡鱼_]

先先上面两位大哥了

net_steven(素狼（W）) 大哥，你是想让我自己在学习中成长，不能太依赖别人吧，你的好意我领了！！非常感谢

vivianfdlpw()大哥，你的解答非常直接而且有效！！谢谢了！！！

[泡泡鱼_]

如果
CREATE PROCEDURE sp_test
(
@Y_A_A_Nums int=0 output,
@Test int output
)
中我要传入参数呢？？

然后在另一个过程中：
create procedure p
as
declare @nums int,@test int
exec sp_test @nums out,@test out--这一句要怎么改呢？？？
select @nums,@test

[泡泡鱼_]

如果我一个存储过程是这样的！
CREATE PROCEDURE sp_test
(
@Member_Code nvarhcar(1000),
@Y_A_A_Nums int=0 output,
@Test int output
)
AS
DECLARE @Sql nvarchar(1000
SET @Sql ='SELECT @Y_A_A_Nums=Y_A_A FROM Init_Week WHERE Member_Code='''+@Member_Code+''' AND StartDay=''2005-7-31'' AND EndDay=''2005-8-6'''
EXEC sp_executesql @Sql,N'@Y_A_A_Nums int out',@Y_A_A_Nums out
IF @Y_A_A_Nums IS NULL
SET @Y_A_A_Nums=0
SET @Test = 1
go

然后在另一个过程中：
create procedure p
as
declare @nums int,@test int
exec sp_test @nums out,@test out--这一句要怎么改呢？？？
select @nums,@test

[vivianfdlpw]

直接使用就行了

[泡泡鱼_]

知道怎么做了咯，呵呵

偶真是笨呀

见笑了见笑了！！！！