CODEWITHSUNDEEP
sqlsql-server-2008t-sqlfunctioncsv
Phil Murray
Phil Murray

Reputation: 6554

TSQL : Return the results of two functions in a single select statement

Is it possible to return the result of two function calls in a select statement?

I have the below TSQL snipped that accepts two csv string which I want to insert into a temp table in order. dbo.Split takes the string and returns a table of values.

Snippet

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

Split

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

Results from calling the split function on both csv strings

enter image description here

When I run the snippet above I get the below error

Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'FROM'.

What I am looking for is as below

Col1     Col2
1        9
2        8
3        7

Is there another way to term the Select statement to return the two values for the Insert?

Upvotes: 1

Views: 1627

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44336

INSERT INTO #tmpMeasureCats(MeasureCatID, SnapshotID)
select t1.value, t2.value
from
(SELECT value, row_number() over (order by (select 1)) rn FROM dbo.Split(',',@MeasureCategoryIDs)) t1
join
(SELECT value, row_number() over (order by (select 1)) rn FROM dbo.Split(',',@SnapshotIDs)) t2
on t1.rn = t2.rn

Upvotes: 3

Hedinn
Hedinn

Reputation: 864

INSERT INTO #tmpMeasureCats
     SELECT value FROM dbo.Split(',',@MeasureCategoryIDs)
INSERT INTO #tmpMeasureCats
SELECT value FROM dbo.Split(',',@SnapshotIDs)

Upvotes: 0

Squirrel5853
Squirrel5853

Reputation: 2406

Assuming your functions are working as expected

INTO #tmpMeasureCats
SELECT 
    A.value as 'MeasureCat', 
    B.value as 'Snapshot'
FROM 
    dbo.Split(',',@MeasureCategoryIDs) A, 
    dbo.Split(',',@SnapshotIDs) B

Upvotes: 0

Related Questions