有一表PERSON,记录了很多人名,动态查询人名,人名本身又是表,然后根据他们的年龄来分类处理,请问我如何得到年龄的值,并赋给变量@temp_age呢?IF EXISTS (SELECT name from sysobjects where name='proc_1' AND type='P')
DROP PROCEDURE proc_1
GOCREATE PROCEDURE proc_1
AS
declare @table_name varchar(30)
declare @sql varchar(200)
declare @temp_age int
BEGIN
declare my_cursor cursor
for
select name from PERSONopen my_cursor
declare @name varchar(40)
fetch next from my_cursor into @name
while(@@fetch_status=0)
begin
print '姓名: ' + @name
set @table_name = @name
set @sql = 'select age from '+@table_name
--再如何用@temp_age得到exec(@sql)呢
--其他地方会根据@temp_age,即年龄的值进行分类
fetch next from my_cursor into @name
end
close my_cursor
deallocate my_cursorEND请大家帮忙解决!
DROP PROCEDURE proc_1
GOCREATE PROCEDURE proc_1
AS
declare @table_name varchar(30)
declare @sql varchar(200)
declare @temp_age int
BEGIN
declare my_cursor cursor
for
select name from PERSONopen my_cursor
declare @name varchar(40)
fetch next from my_cursor into @name
while(@@fetch_status=0)
begin
print '姓名: ' + @name
set @table_name = @name
set @sql = 'select age from '+@table_name
--再如何用@temp_age得到exec(@sql)呢
--其他地方会根据@temp_age,即年龄的值进行分类
fetch next from my_cursor into @name
end
close my_cursor
deallocate my_cursorEND请大家帮忙解决!
begin
print '姓名: ' + @name
set @table_name = @name
set @sql = N'select age from '+@table_name
----使用sp_executesql
exec sp_executesql @sql,N'@temp_age int output',@temp_age output
fetch next from my_cursor into @name
end
while(@@fetch_status=0)
begin
print '姓名: ' + @name
set @table_name = @name
----使用@temp_age = age赋值给变量
set @sql = N'select @temp_age = age from '+@table_name
----使用sp_executesql
exec sp_executesql @sql,N'@temp_age int output',@temp_age output
fetch next from my_cursor into @name
end
注意output关键字,这个关键字告诉sp_executesql,这个@temp_age是要输出值的,即把结果保持到该变量中,sp_executesql结束后该变量就被赋予了新值.
sp_executesql的第一个参数是要执行的SQL字符串,第二个参数是SQL字符串中要用到的局部变量列表,如果局部变量是输出类型的,必须加上output关键字来声明.第三个及之后的参数是SQL要用到的局部变量,数目和顺序与第二个参数中声明的变量数目和顺序相同.
具体请楼主参考帮助,有例子.
请楼主记住:凡是要在字符串中求变量值(无论是不是SQL字符串),只能使用sp_executesql,把要返回值的变量声明为output.