UDF returns table - how to use it in stored procedure

Question

Post reply

UDF returns table - how to use it in stored procedure

Senthil T

Hall of Fame

Points: 3367
More actions
September 21, 2004 at 4:40 pm

#61153

Hi,
I have a user defined function which takes a comma-seperated string as input, splits the string and returns a table with one row for each string.
I did not want to call the function many times to access the data. So I declared a local table inside the stored procedure and tried to use it for processing. But I am not able to.
I tried this.
set @table = fn_splitstring(@strings)
And this.
insert into @table values (select * from fn_splitstring(@strings))
And this.
select * into @table from fn_splitstring(@strings)
None is working. Can anyone tell me what am I doing wrong.
TAI

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Can you expand on what is not working?

Errormessages? Nothing seems to happen? Something happens but not what you expect?

Further, after the function call (ie you have a string to be split) what is your intention with the result?

/Kenneth

Bert De Haes Hall of Fame Points: 3093 More actions · Answer 2

when calling a UDF, you should at least mention the owner of the function. Something like this should work :

insert into @table select * from dbo.fn_splitstring(@strings)

Senthil T Hall of Fame Points: 3367 More actions · Answer 3

Hi,

For "set @table = fn_splitstring(@strings)", the error message was @table was not declared.

For "select * into @table from fn_splitstring(@strings)", the error message was incorrect syntax near @table.

Actually "insert into @table values (select * from fn_splitstring(@strings))", is working, I should not include 'values' in the above statement. "insert into @table (select * from fn_splitstring(@strings))"

But I would like to know what is wrong with the other two. Any ideas?

Thanks.

dbuchan SSCrazy Points: 2328 More actions · Answer 4

You need to supply the full code (SP & UDF).

If the ParseString udf returns a table you may have to think differently.

DB

The systems fine with no users loggged in. Can we keep it that way ?br>

James Truxon SSC Enthusiast Points: 123 More actions · Answer 5

try this:

insert into @table

select * from fn_splitstring(@strings)

or

insert into @table (col1, col2,..., colN)

select src_col1, src_col2,..., src_colN from fn_splitstring(@strings)

Gabriela Nanau SSCommitted Points: 1967 More actions · Answer 6

create function fn_splitstring

( @strings varchar(255)

)

RETURNS @T TABLE( col1 ....)

AS

BEGIN

... you function here

END

and then call it with:

select * from <user_name>.fn_splitstring(<some string&gt

where <user_name> is function's owner and <some string> is whatever actual value you give to that parameter.

Gabriela

Peter Kryszak SSCarpal Tunnel Points: 4368 More actions · Answer 7

For the first method "SET @table = dbo.fn_splitstring..." the error message says it all: you need to declare @table first.

Use:

DECLARE @table TABLE ( item VARCHAR(255) ) -- or similar

SET @table = dbo.fn_splitstring(@strings)

The declaration will probably help with the alternatives too.