内联函数里面无法申明变量?如下:ALTER function dbo.GetEmpIdAndGrpIdBAK
(@EMP_ID NVARCHAR(50))
returns table
as
DECLARE @GRP_ID NVARCHAR(50)
DECLARE @TB TABLE ([USER_ID] NVARCHAR(50) NOT NULL)
INSERT INTO @TB ([USER_ID]) VALUES (@EMP_ID)
SET @GRP_ID=@EMP_ID
WHILE(EXISTS(SELECT TOP 1 GRP_ID FROM OC_GROUP_MEMBER WHERE GMEM_MEM_ID=@GRP_ID))
BEGIN
INSERT INTO @TB SELECT GRP_ID FROM OC_GROUP_MEMBER WHERE GMEM_MEM_ID=@GRP_ID
SELECT @GRP_ID=GRP_ID FROM OC_GROUP_MEMBER WHERE GMEM_MEM_ID=@GRP_ID
END
return(
SELECT [USER_ID] FROM @TB
)
------------------------------------------------------------------------------
存储过程里如何接受其他存储过程返回的结果集:
...
WHERE m.SFLD_MEM_ID IN (SELECT * FROM EXEC GetEmpIdAndGrpId(@EMP_ID)) --不正确WHERE m.SFLD_MEM_ID IN (EXEC GetEmpIdAndGrpId(@EMP_ID))--也不正确
(@EMP_ID NVARCHAR(50))
returns table
as
DECLARE @GRP_ID NVARCHAR(50)
DECLARE @TB TABLE ([USER_ID] NVARCHAR(50) NOT NULL)
INSERT INTO @TB ([USER_ID]) VALUES (@EMP_ID)
SET @GRP_ID=@EMP_ID
WHILE(EXISTS(SELECT TOP 1 GRP_ID FROM OC_GROUP_MEMBER WHERE GMEM_MEM_ID=@GRP_ID))
BEGIN
INSERT INTO @TB SELECT GRP_ID FROM OC_GROUP_MEMBER WHERE GMEM_MEM_ID=@GRP_ID
SELECT @GRP_ID=GRP_ID FROM OC_GROUP_MEMBER WHERE GMEM_MEM_ID=@GRP_ID
END
return(
SELECT [USER_ID] FROM @TB
)
------------------------------------------------------------------------------
存储过程里如何接受其他存储过程返回的结果集:
...
WHERE m.SFLD_MEM_ID IN (SELECT * FROM EXEC GetEmpIdAndGrpId(@EMP_ID)) --不正确WHERE m.SFLD_MEM_ID IN (EXEC GetEmpIdAndGrpId(@EMP_ID))--也不正确
,'Trusted_Connection=yes'
,'exec 库名..存储过程名')但是这种用法限制有点多
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在这个函数中,返回的本地变量名是 @OrderShipperTab。函数主体中的语句将行插入变量 @OrderShipperTab,以生成该函数返回的表结果。下面的查询在其 FROM 子句中引用函数返回的表:SELECT *
FROM LargeOrderShippers( $500 )
WHERE m.SFLD_MEM_ID IN (select SFLD_MEM_ID from openrowset('sqloledb'
,'Trusted_Connection=yes'
,'exec 库名..GetEmpIdAndGrpId('+@EMP_ID+')')
create proc mytest
as
begin
select * from subject
endcreate table #(subjectid int,subjectname varchar(10))
insert # exec mytest
select * from #
(@EMP_ID NVARCHAR(50))
returns table
as
DECLARE @GRP_ID NVARCHAR(50)
DECLARE @TB TABLE ([USER_ID] NVARCHAR(50) NOT NULL)
INSERT INTO @TB ([USER_ID]) VALUES (@EMP_ID)
SET @GRP_ID=@EMP_ID
WHILE(EXISTS(SELECT TOP 1 GRP_ID FROM OC_GROUP_MEMBER WHERE GMEM_MEM_ID=@GRP_ID))
BEGIN
INSERT INTO @TB SELECT GRP_ID FROM OC_GROUP_MEMBER WHERE GMEM_MEM_ID=@GRP_ID
SELECT @GRP_ID=GRP_ID FROM OC_GROUP_MEMBER WHERE GMEM_MEM_ID=@GRP_ID
END
return(
SELECT [USER_ID] FROM @TB
)