从存储过程中的tsql函数保存结果

I am having problems with a stored procedure that I am writing for SQL Server.

我遇到的问题是我正在为SQL Server编写的存储过程。

I am calling a function within the stored procedure which returns a 2 column table with a value. This value I want to assign to a VARCHAR variable to be used throught the rest of my store procedure.

我在存储过程中调用一个函数,它返回一个带有值的2列表。我希望将此值分配给VARCHAR变量,以便在我的其余商店过程中使用。

DECLARE @TEAM VARCHAR(100)
SET @TEAM = (SELECT DISTINCT value fn_split(@foldernarrative, '-') WHERE idx = 0)

The function works on its own but when added to my store procedure I get syntax errors such as

该函数可以自行运行,但是当我添加到我的商店程序时,我会收到语法错误,例如

Server: Msg 170, Level 15, State 1, Line 94
Line 94: Incorrect syntax near '('.

I'm quite new to this and I can't see why this is a problem.

我对此很陌生,我不明白为什么这是一个问题。

Any help would be greatfully recieved.

任何帮助都会得到很好的回复。

Thanks.

4 个解决方案

#1

I think you're missing a FROM keyword when selecting from your table-valued function.

我认为从表值函数中选择时,您缺少一个FROM关键字。

Try this:

DECLARE @TEAM VARCHAR(100)
SET @TEAM = (SELECT DISTINCT value FROM fn_split(@foldernarrative, '-') WHERE idx = 0)

#2

As dariom pointed out but you will also need to add the object owner if fn_split is a function.

正如dariom指出的那样,如果fn_split是一个函数,你还需要添加对象所有者。

DECLARE @TEAM VARCHAR(100)
SET @TEAM = (SELECT DISTINCT value FROM dbo.fn_split(@foldernarrative, '-') WHERE idx = 0)

#3

Of course this code will only work if your function cannot ever return more than one row.

当然,只有当您的函数不能返回多行时,此代码才有效。

#4

You might add an IsNull around @foldernarrative in case you anticipate ever passing NULL to it, either purposely or accidentally.

您可以在@foldernarrative周围添加一个IsNull,以防您有意或无意地将NULL传递给它。

#1