--不能用表变量,可以有类似这样的方法--示例存储过程 create proc p_test as select name from sysobjects go--调用 create table #t(name sysname) insert #t exec p_test select * from #t
如樓上所說,直接用 select * from 臨時表處理,在程式中用recordset對象提取。如果有多個select,則在程式中用recordset對象的NextRecordset方法提取。
函數可用表變量:CREATE FUNCTION dbo.FunSplitStringToAraay(@vchString varchar(1000),@vchSplit varchar(10)) RETURNS @tabArray table ( string varchar(100) ) AS BEGIN ... INSERT INTO @tabArray(string) SELECT 'abc' ... RETURN END
create proc p_test as select name from sysobjects select ... from .... select ... from ... go
--另一个方法是用游标变量--存储过程救命 create proc p_test @cu1 cursor varying output, @cu2 cursor varying output as set @cu1=cursor for select top 5 name from sysobjects where xtype='U' set @cu2=cursor for select top 5 name from sysobjects where xtype='V' open @cu1 open @cu2 go--调用示例 declare @cu1 cursor,@cu2 cursor,@name sysname exec p_test @cu1=@cu1 out,@cu2=@cu2 outprint '--------游标1----------------' fetch next from @cu1 into @name while @@fetch_status=0 begin print @name fetch next from @cu1 into @name end close @cu1 deallocate @cu1print '--------游标2----------------' fetch next from @cu2 into @name while @@fetch_status=0 begin print @name fetch next from @cu2 into @name end close @cu2 deallocate @cu2 go--删除测试 drop proc p_test/*--测试结果 --------游标1---------------- t by_huikao by_huikaobukao B cj_banji --------游标2---------------- syssegments sysconstraints--*/
create proc p_test
as
select name from sysobjects
go--调用
create table #t(name sysname)
insert #t exec p_test
select * from #t
RETURNS @tabArray table
(
string varchar(100)
)
AS
BEGIN
... INSERT INTO @tabArray(string)
SELECT 'abc'
...
RETURN
END
as
select name from sysobjects
select ... from ....
select ... from ...
go
create proc p_test
@cu1 cursor varying output,
@cu2 cursor varying output
as
set @cu1=cursor for select top 5 name from sysobjects where xtype='U'
set @cu2=cursor for select top 5 name from sysobjects where xtype='V'
open @cu1
open @cu2
go--调用示例
declare @cu1 cursor,@cu2 cursor,@name sysname
exec p_test @cu1=@cu1 out,@cu2=@cu2 outprint '--------游标1----------------'
fetch next from @cu1 into @name
while @@fetch_status=0
begin
print @name
fetch next from @cu1 into @name
end
close @cu1
deallocate @cu1print '--------游标2----------------'
fetch next from @cu2 into @name
while @@fetch_status=0
begin
print @name
fetch next from @cu2 into @name
end
close @cu2
deallocate @cu2
go--删除测试
drop proc p_test/*--测试结果
--------游标1----------------
t
by_huikao
by_huikaobukao
B
cj_banji
--------游标2----------------
syssegments
sysconstraints--*/
sql server 现在还不支持传入表变量的参数!的让老比尔加油啊:D学习中