对不可以。你可以用函数:下例在 Northwind 数据库中创建一个返回 table 的函数:CREATE FUNCTION LargeOrderShippers ( @FreightParm money ) RETURNS @OrderShipperTab TABLE ( ShipperID int, ShipperName nvarchar(80), OrderID int, ShippedDate datetime, Freight money ) AS BEGIN INSERT @OrderShipperTab SELECT S.ShipperID, S.CompanyName, O.OrderID, O.ShippedDate, O.Freight FROM Shippers AS S INNER JOIN Orders AS O ON S.ShipperID = O.ShipVia WHERE O.Freight > @FreightParm RETURN END
游标比较麻烦:create proc a @1 cursor varying out, @2 cursor varying out as declare b cursor local for select * from table1 declare c cursor local for select * from table1 open b open c set @1=b set @2=c godeclare @a cursor,@b cursorexec a @a out,@b out fetch @a fetch @b fetch @b fetch @b fetch @aclose @a close @bdeallocate @a deallocate @bdrop proc a
请能够比较详细的说明一下你的问题吗?看起来有些模糊,不知道你为什么要用table变量呢?
函数可以 create function test (@djbh char(10)) returns @aa table (a char(10),b char(50)) as begin insert into @aa select '1','2' return end
象其他的数据类型一样,如以下的过程: create procedure test(@t varchar(30) output)as--使用varchar数据类型的参数 begin select @t=substring(@t,3,5);--参数的处理并返回 end go create procedure test1(@t table(id int,data float) output) as--table类型做参数 begin update @t set data=0.0 where 1=1;--使用table类型参数(实际上是不能这样做的) end--实际上table类型不能传进来, --如果table是SQL server中的一个类型,应该能做参数的,但现在是不能做参数,不知能否实现 --这样的做法??
这样做的话,能处理很多的运算,比如说矩阵运算就很方便了,可以对表变量的交叉操作. 如以下做法: begin declare @t1 table(id int,data float); declare @t2 table(id int,data float); update @t1 set data=(a.data-b.data)/case b.data when 0 then 1 else b.data end from @t1 a,@t2 b where a.id=b.id end
RETURNS @OrderShipperTab TABLE
(
ShipperID int,
ShipperName nvarchar(80),
OrderID int,
ShippedDate datetime,
Freight money
)
AS
BEGIN
INSERT @OrderShipperTab
SELECT S.ShipperID, S.CompanyName,
O.OrderID, O.ShippedDate, O.Freight
FROM Shippers AS S INNER JOIN Orders AS O
ON S.ShipperID = O.ShipVia
WHERE O.Freight > @FreightParm
RETURN
END
@1 cursor varying out,
@2 cursor varying out
as
declare b cursor local for select * from table1
declare c cursor local for select * from table1
open b
open c
set @1=b
set @2=c
godeclare @a cursor,@b cursorexec a @a out,@b out
fetch @a
fetch @b
fetch @b
fetch @b
fetch @aclose @a
close @bdeallocate @a
deallocate @bdrop proc a
create function test (@djbh char(10))
returns @aa table (a char(10),b char(50))
as
begin
insert into @aa
select '1','2' return
end
create procedure test(@t varchar(30) output)as--使用varchar数据类型的参数
begin
select @t=substring(@t,3,5);--参数的处理并返回
end
go
create procedure test1(@t table(id int,data float) output) as--table类型做参数
begin
update @t set data=0.0 where 1=1;--使用table类型参数(实际上是不能这样做的)
end--实际上table类型不能传进来,
--如果table是SQL server中的一个类型,应该能做参数的,但现在是不能做参数,不知能否实现
--这样的做法??
没有说过程不能呀
如果在过程里即使加入了varying output这句话,也还是不能的?
'存储过程--参数的数据类型。所有数据类型(包括 text、ntext 和 image)均可以用作存储过程的参数'
说明table数据类型能做过程的参数,那应该怎么写呢?
谢谢
如以下做法:
begin
declare @t1 table(id int,data float);
declare @t2 table(id int,data float);
update @t1 set
data=(a.data-b.data)/case b.data when 0 then 1 else b.data end
from @t1 a,@t2 b
where a.id=b.id
end
"非标量类型(如 table 或 cursor)。"
1.有关返回 table 数据类型的用户定义函数:
CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
(
ShipperID int,
ShipperName nvarchar(80),
OrderID int,
ShippedDate datetime,
Freight money
)
AS
BEGIN
INSERT @OrderShipperTab
SELECT S.ShipperID, S.CompanyName,
O.OrderID, O.ShippedDate, O.Freight
FROM Shippers AS S INNER JOIN Orders AS O
ON S.ShipperID = O.ShipVia
WHERE O.Freight > @FreightParm
RETURN
END调用:
SELECT *
FROM LargeOrderShippers( $500 )
2.表值函数(用于解决视图无法接受参数)
创建筛选汇总数据的内嵌函数:
CREATE FUNCTION fn_QuarterlySalesByStore
(
@StoreID int
)
RETURNS table
AS
RETURN (
SELECT *
FROM SalesDB.dbo.vw_QuarterlySales
WHERE StoreID = @StoreID
)然后用户可从内嵌函数进行选择以获得其特定商店的数据:
SELECT *
FROM fn_QuarterlySalesByStore( 14432 )