SQL 在sql函数中声明变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4215052/
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
declare variable in a sql function
提问by Amit
I have a sql function and i need to declare few variables in that function. Please advise how can i achieve this.
我有一个 sql 函数，我需要在该函数中声明几个变量。请告知我如何实现这一目标。
For example i need to put -->
例如我需要把 -->
Declare @ClientResult TABLE(
RowIndex int identity(1,1),
SplitText varchar(50)
)
in the below function.
在下面的函数中。
create FUNCTION [dbo].CLIENT_SHIPPINGREPORTDATA_Function_Test
(
@CLIENTPK_NEW TABLE,
@CGNEEPK TABLE
@type varchar(100)
)
RETURNS TABLE
AS
RETURN
SELECT distinct
OP_PartNum,
OP_PK
FROM Client_whsPallet pallet
I am using sql server 2005
我正在使用 sql server 2005
Thanks
谢谢
采纳答案by AdaTheDev
What you are after is a multi-statement table function
你所追求的是一个多语句表函数
e.g.
例如
CREATE FUNCTION dbo.fxnExample (@Param INTEGER)
RETURNS @Results TABLE(FieldA VARCHAR(50))
AS
BEGIN
INSERT @Results
SELECT SomeField
FROM Somewhere
WHERE ParamField = @Param
RETURN
END
This is different to your current function which is called an "inline table valued function" and you should be aware of the differences as this could cause performance issues if you switch to the multi-statement approach. My advice would be to try and use inline table valued functions wherever possible. I recommend you checking out these articles which go into detail:
这与您当前称为“内联表值函数”的函数不同，您应该注意这些差异，因为如果切换到多语句方法，这可能会导致性能问题。我的建议是尽可能尝试使用内联表值函数。我建议您查看这些文章，其中详细介绍了：
Multi-statement Table Valued Function vs Inline Table Valued Function
http://blogs.msdn.com/b/psssql/archive/2010/10/28/query-performance-and-multi-statement-table-valued-functions.aspx
http://sqlbits.com/Agenda/event6/High_performance_functions/default.aspx
多语句表值函数与内联表值函数
http://blogs.msdn.com/b/psssql/archive/2010/10/28/query-performance-and-multi-statement-table-valued-functions.aspx
http://sqlbits.com/Agenda/event6/High_performance_functions/default.aspx
回答by Justin Swartsel
In SQL Server you can't declare variables inside of an inline table-Valued function. You'll need to create a multi-statement table valued function if you really need to declare variables in it. You would do something like this:
在 SQL Server 中，您不能在内联表值函数内声明变量。如果您确实需要在其中声明变量，则需要创建一个多语句表值函数。你会做这样的事情：
CREATE FUNCTION [dbo].CLIENT_SHIPPINGREPORTDATA_Function_Test
(
@CLIENTPK_NEW TABLE, @CGNEEPK TABLE @type varchar(100)
)
RETURNS @output TABLE (OP_PartNum int, OP_PK int)
AS BEGIN
Declare @ClientResult TABLE( RowIndex int identity(1,1), SplitText varchar(50) )
/* more code here */
RETURN
END
Not knowing what exactly it is you are trying to do, I would see if there is away around using a multi-statement function though as you will see performance decrease.
不知道您到底要做什么，我会看看是否可以使用多语句函数，尽管您会看到性能下降。
回答by Anthony Faull
Compare these equivalent code samples. They show the syntax differences between inline and multistatement table-valued functions.
比较这些等效的代码示例。它们显示了内联和多语句表值函数之间的语法差异。
CREATE FUNCTION [dbo].Inline (@type varchar(100))
RETURNS TABLE
AS
RETURN
SELECT distinct name
FROM sysobjects
WHERE type = @type
GO
CREATE FUNCTION [dbo].Multistatement (@type varchar(100))
RETURNS @results TABLE (name sysname)
AS
BEGIN
INSERT @results (name)
SELECT distinct name
FROM sysobjects
WHERE type = @type
RETURN
END
回答by Ghidello
As suggested by AdaTheDev you can create a multi-statement function for returning a table from a function.
Otherwise if you need to create a table inside the function you can create a new temporary table prefixing its name with an #
正如 AdaTheDev 所建议的，您可以创建一个多语句函数来从函数返回一个表。
否则，如果您需要在函数内创建一个表，您可以创建一个新的临时表，在其名称前加上 #
create table #TableNAme (FieldA Varchar(5))
