saving a result from a tsql function in a stored procedure

Question

Hi,

I am having problems with a stored procedure that I am writing for 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.

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.

Answer 1

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

Try this:

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

Answer 2

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

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

Answer 3

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

Answer 4

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