How to Consume Return Values From Table Valued Function

Question

Post reply

How to Consume Return Values From Table Valued Function

ennawarner

SSC Rookie

Points: 41
More actions
July 9, 2014 at 5:31 am

#287790

Hi All,
I have a table valued function that splits a string based on a delimiter and produces a set of discrete values. The function:
ALTER FUNCTION [dbo].[Split] (@sep char(1), @s-2 varchar(8000))
RETURNS table
AS
RETURN (
WITH splitter_cte AS (
SELECT CHARINDEX(@sep, @s-2) as pos, 0 as lastPos
UNION ALL
SELECT CHARINDEX(@sep, @s-2, pos + 1), pos
FROM splitter_cte
WHERE pos > 0
)
SELECT SUBSTRING(@s, lastPos + 1,
case when pos = 0 then 80000
else pos - lastPos -1 end) as OutputValues
FROM splitter_cte
) [/b]
The output:
OutputValues
1
2
3
4
5
6
My question is how best to use these values in my next statement, which will do inserts. I was thinking to avoid a cursor, but I'm not sure how best to proceed.
Thanks!
Bill

Viewing 6 posts - 1 through 5 (of 5 total)

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

Eirikur Eiriksson SSC Guru Points: 182879 More actions · Answer 1

Quick thought, place the function in a cte and use the output in the insert.

😎

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Treat it as any other table source: SELECT * FROM dbo.MyFunction(param, param)

Having said that, the rCTE string splitter is known to perform poorly against other methods. You can read about it in this article [/url]which offers a much faster alternative.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ennawarner SSC Rookie Points: 41 More actions · Answer 3

How do I do an insert for each row, without iterating over the available rows?

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 4

bill.warner7 (7/9/2014)
How do I do an insert for each row, without iterating over the available rows?

if runtime temp table is ok then you can use below:

SELECT * INTO #tempTable FROM dbo.MyFunction(param, param)

If table is already present with same return values (columns) then you can use:

INSERT INTO TABLE (column1) SELECT column1 FROM dbo.MyFunction(param, param)

Thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

INSERT INTO TargetTable (<<columnlist>>)

SELECT <<columnlist>>

FROM SourceTable

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden