StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTSQL : Return the results of two functions in a single select statement
text
Body
copied!<p>Is it possible to return the result of two function calls in a select statement?</p> <p>I have the below TSQL snipped that accepts two csv string which I want to insert into a temp table in order. <code>dbo.Split</code> takes the string and returns a table of values.</p> <p><strong>Snippet</strong></p> <pre><code>DECLARE @MeasureCategoryIDs as nvarchar(100) DECLARE @SnapshotIDs as nvarchar(100) SET @MeasureCategoryIDs = '1,2,3' SET @SnapshotIDs = '9,8,7' CREATE TABLE #tmpMeasureCats ( MeasureCatID int PRIMARY KEY NOT NULL, SnapshotID int NOT NULL ) INSERT INTO #tmpMeasureCats SELECT value FROM dbo.Split(',',@MeasureCategoryIDs), value FROM dbo.Split(',',@SnapshotIDs) DROP TABLE #tmpMeasureCats </code></pre> <p><strong>Split</strong></p> <pre><code>ALTER FUNCTION [dbo].[Split] ( @Delimiter varchar(5), @List varchar(8000) ) RETURNS @TableOfValues table ( RowID smallint IDENTITY(1,1), [Value] varchar(50) ) AS BEGIN DECLARE @LenString int WHILE len( @List ) > 0 BEGIN SELECT @LenString = (CASE charindex( @Delimiter, @List ) WHEN 0 THEN len( @List ) ELSE ( charindex( @Delimiter, @List ) -1 ) END ) INSERT INTO @TableOfValues SELECT substring( @List, 1, @LenString ) SELECT @List = (CASE ( len( @List ) - @LenString ) WHEN 0 THEN '' ELSE right( @List, len( @List ) - @LenString - 1 ) END ) END RETURN END </code></pre> <p><strong>Results from calling the split function on both csv strings</strong></p> <p><img src="https://i.stack.imgur.com/AcALz.png" alt="enter image description here"></p> <p>When I run the snippet above I get the below error</p> <blockquote> <p>Msg 156, Level 15, State 1, Line 15<br> Incorrect syntax near the keyword 'FROM'.</p> </blockquote> <p>What I am looking for is as below</p> <pre><code>Col1 Col2 1 9 2 8 3 7 </code></pre> <p>Is there another way to term the Select statement to return the two values for the Insert?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload