急！存储过程输出问题！

[ysw8u8]

CREATE procedure test2
@NumID int,
@OpenQiu int,
@state varchar(100) output
as
declare @s1 nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000)
set @s1='declare @LuckPoint char(10),@SystemLuckPoint char(10),@sss varchar(100) '

set @s2='select @LuckPoint=sum(Luck'+cast(@OpenQiu as char)+') from TestList where NumID='+cast(@NumID as char)+' and ID>100 '
set @s2=@s2+' select @SystemLuckPoint=sum(Luck'+cast(@OpenQiu as char)+') from TestList where NumID='+cast(@NumID as char)+' and UserType=2 '
set @s3='set @sss=@LuckPoint+''|''+@SystemLuckPoint'
set @s3='set '+@state+'=@LuckPoint+''|''+@SystemLuckPoint'

print(@s1+' '+@s2+' '+@s3)
EXEC (@s1+' '+@s2+' '+@s3)

在这里我怎样才能把 @sss 的值赋给 @state并输出呢？我是用ASP调用的，想把查询的结果返回到ASP中！
我试过这样做，但行不通：
set @s3='set '+@state+'=@LuckPoint+''|''+@SystemLuckPoint'
还请高手指点，万分感谢！！！！

[子陌红尘]

CREATE procedure test2 
@NumID int, 
@OpenQiu int, 
@state varchar(100) output 
as 
begin
    declare @s1 nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000) 
    set @s1='declare @LuckPoint char(10),@SystemLuckPoint char(10),@sss varchar(100) ' 
    
    set @s2='select @LuckPoint=sum(Luck'+cast(@OpenQiu as char)+') from TestList where NumID='+cast(@NumID as char)+' and ID> 100 ' 
    set @s2=@s2+'   select @SystemLuckPoint=sum(Luck'+cast(@OpenQiu as char)+') from TestList where NumID='+cast(@NumID as char)+' and UserType=2 ' 
    set @s3='set @sss=@LuckPoint+'' |''+@SystemLuckPoint' 
    set @s3='set @state=@LuckPoint+'' |''+@SystemLuckPoint' 
    set @s1=@s1+' '+@s2+' '+@s3
    
    exec sp_executesql @s1,N'@state varchar(100) out',@state out
end
go

[wzy_love_sly]

怎么楼主 set @s1='declare ....',这样也行吗？

[wzy_love_sly]

总之
exec sp_executesql @sql,
N'@var type output,@var type output',
@var output,@var type output

[ysw8u8]

set @s1='declare ....' 这样写当然可以了！ libin_ftsafe ，你好，你刚才的那段代码我试过了，还是不行，l输出如果：
declare @LuckPoint char(10),@SystemLuckPoint char(10),@sss varchar(100)
select @LuckPoint=sum(Luck20) from Luck28Join where NumID=11 and ID> 100
select @SystemLuckPoint=sum(Luck20 ) from Luck28Join where NumID=11 and UserType=2
set @state=@LuckPoint+' |'+@SystemLuckPoint

我在ASP里还是得到这个字符串，返回一个空值！！

[wzy_love_sly]

create table tb(id int,sumamt int)
insert tb select 2,900
insert tb select 3,100

declare @sql varchar(8000)
set @sql = ' declare @s nvarchar(4000),@count int '
set @sql = @sql+' set @s=''select @count=count(*) from tb '''
set @sql = @sql+' exec sp_executesql @s,N''@count int output'',@count output'
set @sql = @sql+' select @count'
select @sql

老兄，你如果会正常的 ，拼起来不还是一样的

输出：
declare @s nvarchar(4000),@count int set @s='select @count=count(*) from tb ' exec sp_executesql @s,N'@count int output',@count output select @count