如何在 SQL Server 2005 中的函数中声明表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6110069/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to declare table in function in SQL Server 2005
提问by Dhiraj
I need to create a function do to certain processing on data to be returned to a SQL view.
我需要创建一个函数来对要返回到 SQL 视图的数据进行某些处理。
I have designed the function as below, but am getting error saying
我设计了如下功能，但出现错误提示
Must declare the table variable "@FINALRESULTS"
必须声明表变量“@FINALRESULTS”
although I have defined it as table.
尽管我已将其定义为表。
Can you please help me making this function executable.
你能帮我使这个函数可执行吗？
Appreciate your help!!
感谢你的帮助！！
CREATE FUNCTION dbo.names(@CUSTID varchar(20), @effdt varchar(20))
RETURNS @FinalResults1 (Name1 nvarchar(254), Name2 nvarchar(254))
AS
BEGIN
DECLARE TABLE @FinalResults (Name254 nvarchar(254), SRNO nvarchar(3))
CREATE TABLE @FinalResults (Name254 nvarchar(254), SRNO nvarchar(3))
INSERT INTO @FinalResults
SELECT(C.NAME1),ROW_NUMBER() OVER(ORDER BY A.SEQ_NBR)
FROM PS_ARB_CU_CLST_STN A , PS_ARB_CU_STATIONS C
WHERE A.EFF_STATUS = 'A'
AND A.EFFDT = (SELECT MAX(B.EFFDT)
FROM PS_ARB_CU_CLST_STN B
WHERE A.SETID = B.SETID
AND A.CUST_ID = B.CUST_ID
AND B.EFFDT <= @effdt)
AND A.SETID = C.SETID
AND A.ARB_STATION_ID =C.CUST_ID
AND A.CUST_ID = @CUSTID
AND C.EFFDT = (SELECT MAX(D.EFFDT)
FROM PS_ARB_CU_STATIONS D
WHERE C.CUST_ID = D.CUST_ID
AND D.SETID = C.SETID
AND D.EFFDT <= @effdt)
ORDER BY
A.SEQ_NBR
DECLARE @Name nvarchar(254), @FULLNAME1 nvarchar(128), @FREEZENAME1 nvarchar(10), @append NVARCHAR (254)
DECLARE @FULLNAME254 nvarchar(254), @FULLNAME2 nvarchar(128), @FREEZENAME2 nvarchar(10), @COUNT INT, @i INT
SET @Name = ''
SET @FREEZENAME1 = 'FALSE'
SET @FREEZENAME2 = 'FALSE'
SET @FULLNAME1 = ''
SET @FULLNAME2 = ''
SET @FULLNAME254 = ''
SET @COUNT = 0
SET @i = 0
SELECT @COUNT = COUNT(*) FROM @FinalResults
WHILE @i < @COUNT
BEGIN
IF @FULLNAME1 = ''
IF(LEN((SELECT NAME254 FROM @FinalResults WHERE SRNO = @i )+ '/')<= 40 AND @FREEZENAME1 = 'FALSE' )
SET @FULLNAME1 = (SELECT NAME254 FROM @FinalResults WHERE SRNO = @i + '/');
ELSE
SET @FREEZENAME1 = 'TRUE';
END IF
ELSE
IF (LEN(@FULLNAME1 +(SELECT NAME254 FROM @FinalResults WHERE SRNO = @i )+ '/') <=40 AND @FREEZENAME1 = 'FALSE' )
SET @FULLNAME1 = (@FULLNAME1 +(SELECT NAME254 FROM @FinalResults WHERE SRNO = @i )+ '/') ;
ELSE
SET @FREEZENAME1 = 'TRUE';
IF @FULLNAME2 = ''
IF (LEN((SELECT NAME254 FROM @FinalResults WHERE SRNO = @i )+ '/')<= 40 AND @FREEZENAME2 = 'FALSE' )
SET @FULLNAME2 = ((SELECT NAME254 FROM @FinalResults WHERE SRNO = @i )+ '/') ;
ELSE
SET @FREEZENAME2 = 'TRUE';
END IF
ELSE
IF (LEN(@FULLNAME2 +(SELECT NAME254 FROM @FinalResults WHERE SRNO = @i )+ '/') <=40 AND @FREEZENAME2 = 'FALSE')
SET @FULLNAME2 = (@FULLNAME2 +(SELECT NAME254 FROM @FinalResults WHERE SRNO = @i )+ '/') ;
ELSE
SET @FREEZENAME2 = 'TRUE';
END-IF
END-IF
END-IF
END-IF
IF @append = ''
@append = (SELECT NAME254 FROM @FinalResults WHERE SRNO = @i );
Else
@append = @append + '/'+ (SELECT NAME254 FROM @FinalResults WHERE SRNO = @i );
END-IF
END-IF
SET @i = @i +1
END
END-WHILE
If (Len(@append) < 40)
@FULLNAME1 = RTrim(@FULLNAME2, '/');
End-If;
If ((Len(@append) > 40) And
(Len(@append) < 80))
@FULLNAME2 = RTrim(@FULLNAME2, '/');
End-If;
BEGIN
INSERT INTO #FinalResults1 VALUES ( @FULLNAME1, @FULLNAME2)
END
RETURN
END
GO
SELECT Name1
, Name2
FROM @FinalResults1
回答by Martin Smith
DECLARE TABLE @FinalResults (Name254 nvarchar(254), SRNO nvarchar(3))
CREATE TABLE @FinalResults (Name254 nvarchar(254), SRNO nvarchar(3))
Should just be
应该只是
DECLARE @FinalResults TABLE (Name254 nvarchar(254), SRNO nvarchar(3))
Additionally the return type of the function should be
此外，函数的返回类型应该是
RETURNS @FinalResults1 TABLE (Name1 nvarchar(254), Name2 nvarchar(254))
There seem to be still more syntax errors (why are you using END-IF?) but that should answer the question asked and I have no intention of going through fixing them all.
似乎还有更多的语法错误（你为什么使用END-IF？）但这应该可以回答所提出的问题，我无意全部修复它们。
