先将存储过程返回集取道临时表再select:create table #t(s varchar(8000))--与存储过程返回集结构相同的临时表
insert into #t(s) exec funa 'a'select * from table1
where table1.id in(select s from #t)drop table #t
insert into #t(s) exec funa 'a'select * from table1
where table1.id in(select s from #t)drop table #t
-----------------------------------------------------------------------------------------------------------------------------------------生成测试数据
create table test(id int,name varchar(20))
insert into test select 1,'AAAA'
insert into test select 2,'BBBB'
go--创建带有OUTPUT参数的存储过程
create procedure sp_test(@name1 varchar(20),@name2 varchar(20) output)
as
begin
select @name2=name from test where id=(select id+1 from test where name=@name1)
return
end
go--定义变量,用于存放存储过程的OUTPUT返回参数
declare @name varchar(20)
exec sp_test 'AAAA',@name output--使用存储过程OUTPUT返回参数
select * from test where name=@name
go--清除测试环境
drop procedure sp_test
drop table test
不过, 不爽的是, 出现了
服务器: 消息 195,级别 15,状态 10,行 1
'IsMySonType' 不是可以识别的 函数名。
再请各位大大指点一二!IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'IsMySonType' AND type = 'FN')
DROP Function IsMySonType
GOCREATE Function IsMySonType (@Father varchar(50),@SonID varchar(50)) Returns varchar(50) AS
begin
Declare @TempSon varchar(50),@TempFather varchar(50)
Set @TempFather = @SonID
while @TempFather <>'0'
begin
Set @TempSon = @TempFather
execute GetFatherID @TempSon , @FatherID = @TempFather OUTPUT
if @TempFather = @Father
begin
return 'Yes'
end
end
return 'No'
end
GODeclare @sResult varchar(50)
set @sResult = IsMySonType('11', '111')
print @sResult
GO
在此谢谢各位大大!