Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to Consume Return Values From Table Valued Function Expand / Collapse
Author
Message
Posted Wednesday, July 9, 2014 5:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 6:39 AM
Points: 7, Visits: 20
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 varchar(8000))
RETURNS table
AS
RETURN (
WITH splitter_cte AS (
SELECT CHARINDEX(@sep, @s) as pos, 0 as lastPos
UNION ALL
SELECT CHARINDEX(@sep, @s, 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]
[/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
Post #1590677
Posted Wednesday, July 9, 2014 5:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:25 AM
Points: 2,017, Visits: 5,495
Quick thought, place the function in a cte and use the output in the insert.
Post #1590686
Posted Wednesday, July 9, 2014 5:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:44 AM
Points: 6,750, Visits: 13,898
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 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
Exploring Recursive CTEs by Example Dwain Camps
Post #1590687
Posted Wednesday, July 9, 2014 6:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 6:39 AM
Points: 7, Visits: 20
How do I do an insert for each row, without iterating over the available rows?
Post #1590697
Posted Wednesday, July 9, 2014 6:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:27 AM
Points: 1,130, Visits: 1,390
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
Post #1590699
Posted Wednesday, July 9, 2014 6:23 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:44 AM
Points: 6,750, Visits: 13,898
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1590700
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse