Get number of fields from dynamic sql query (with execute statement) in sql server

Question

I've the following query in sql-server: I want to find the number of fields in the query output.

SET @query1 = 'SELECT Strain_ID, COLNAMES as Markers, ' + @cols + '

FROM 
 (  -- Source data for pivoting
    SELECT CONCAT(Chromosome,''_'',Locus) ChrLocus,Strain_ID,Markers, COLNAMES, sort
    FROM ProgenyRawJuly14   
    CROSS APPLY(VALUES (1, MarkerSet1,''MarkerSet1''),(2, MarkerSet2,''MarkerSet2''),(3, Parent1_Marker,''Parent1_Marker''),(4, Parent2_MarkerA,''Parent2_MarkerA''),
    (5, Parent2_MarkerB,''Parent2_MarkerB''))
    AS COLUMNNAMES(Sort, Markers,COLNAMES)
    UNION 
    SELECT CONCAT(Chromosome,''_'',Locus) ChrLocus,Strain_ID,Markers, COLNAMES, sort 
    FROM ParentRawTableJuly14 
    CROSS APPLY(VALUES (1, MarkerSet1,''MarkerSet1''),(2, MarkerSet2,''MarkerSet2''),(3, Parent1_Marker,''Parent1_Marker''),(4, Parent2_MarkerA,''Parent2_MarkerA''),
    (5, Parent2_MarkerB,''Parent2_MarkerB''))
    AS COLUMNNAMES(Sort, Markers,COLNAMES)

  ) x

 PIVOT 
 (
     --Defines the values in each dynamic columns
     min(Markers)
     -- Get the names from the @cols variable to show as column
     FOR ChrLocus IN ('+ @cols +')
 ) p 

order by Strain_ID, sort;'

EXEC SP_EXECUTESQL @query= @query1, @params= N'@queryO NVARCHAR(MAX) OUTPUT',              
@queryO= @queryO OUTPUT; 

select @queryO;

How do I get the number of fields in @queryO so that I can create a temporary table with that number of columns?

score 1 · Accepted Answer · edited May 23 '17 at 12:14

This seems kind of a round about way to do it... but it works:

SET @query1 = 'SELECT Strain_ID, COLNAMES as Markers, ' + @cols + '
into #temp;
FROM 
 (  -- Source data for pivoting
    SELECT CONCAT(Chromosome,''_'',Locus) ChrLocus,Strain_ID,Markers, COLNAMES, sort
    FROM ProgenyRawJuly14   
    CROSS APPLY(VALUES (1, MarkerSet1,''MarkerSet1''),(2, MarkerSet2,''MarkerSet2''),(3, Parent1_Marker,''Parent1_Marker''),(4, Parent2_MarkerA,''Parent2_MarkerA''),
    (5, Parent2_MarkerB,''Parent2_MarkerB''))
    AS COLUMNNAMES(Sort, Markers,COLNAMES)
    UNION 
    SELECT CONCAT(Chromosome,''_'',Locus) ChrLocus,Strain_ID,Markers, COLNAMES, sort 
    FROM ParentRawTableJuly14 
    CROSS APPLY(VALUES (1, MarkerSet1,''MarkerSet1''),(2, MarkerSet2,''MarkerSet2''),(3, Parent1_Marker,''Parent1_Marker''),(4, Parent2_MarkerA,''Parent2_MarkerA''),
    (5, Parent2_MarkerB,''Parent2_MarkerB''))
    AS COLUMNNAMES(Sort, Markers,COLNAMES)

  ) x

 PIVOT 
 (
     --Defines the values in each dynamic columns
     min(Markers)
     -- Get the names from the @cols variable to show as column
     FOR ChrLocus IN ('+ @cols +')
 ) p 

order by Strain_ID, sort;
SELECT @queryO=COUNT(*) FROM tempdb.sys.columns WHERE object_id = object_id(''tempdb..#temp'');
select * from #temp;'

EXEC SP_EXECUTESQL @query= @query1, @params= N'@queryO NVARCHAR(MAX) OUTPUT',              
@queryO= @queryO OUTPUT; 

select @queryO;

It seems though like it would be more efficient if you could just get the number of columns when generating your list for the @cols variable.

You also might want to look into using OPENROWSET if you just create your table directly: Insert results of a stored procedure into a temporary table

Do you have any resource to translate this query for PHP code? — Vishnu Ramasubramanian, Apr 23 '15 at 20:43

Get number of fields from dynamic sql query (with execute statement) in sql server

1 Answers1