asked on

how to return dynamic column name in stored procedure?

in my stored procedure, i group the result set to return multiple groups like 'Sheet1', 'Sheet2'

Here is my the grouping return in my SP:
select [Sheet + @loop] = 'Sheet' + Convert(varchar(10), @loop), * from @Result result
WHERE result.State = (Select top 1 State from @Group where RowID = @loop) AND

SELECT @loop = @loop + 1
END

How can i make the first column name as 'Sheet1', 'Sheet2', 'Sheet3', etc.
I know my syntax is wrong as [Sheet + @loop], but just want to give u some idea

Rimvis

Hi hongclub,

You will have to use dynamic SQL for that. Please see example bellow. If parameters for you query are coming from external sources, make sure you are validating it against SQL injection attacks.

DECLARE @sSQL NVARCHAR(MAX)
SET @sSQL = N'select [Sheet' + Convert(varchar(10), @loop) +'] = ''Sheet' + Convert(varchar(10), @loop)+ ', * from ' + @Result + ' result '
			+ N'WHERE result.State = (Select top 1 State from ' + @Group + ' where RowID = ' + Convert(varchar(10), @loop)+ ') AND ...'
			
EXEC (@sSQL)

Open in new window

hongclub

ASKER

thanks, i tired it, however there are two issues i am facing

(1) it returned me error, it said 'Must declare the scalar variable "@Result", but i have that @Result table declare also, so i twist my code to this
WHILE (@loop < = (Select count(*) from @Group))
-- WHILE (@loop < = 2)
BEGIN
DECLARE @sSQL NVARCHAR(MAX)
SET @sSQL = N'select [Sheet' + Convert(varchar(10), @loop) +'] = ''Sheet' + Convert(varchar(10), @loop)+ ', * from @Result result '
+ N'WHERE result.State = (Select top 1 State from @Group ' + ' where RowID = ' + Convert(varchar(10), @loop)+ ') AND '

EXEC (@sSQL)
END

(2) after i change, the sp is so slow, it took 10 seconds to run, and eventually i stop executing the SP, is it because it is in the while loop

hongclub

ASKER

any suggestion, can't believe it is so complicated to generate a dynamic column name

ASKER CERTIFIED SOLUTION

Rimvis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hongclub

ASKER

thanks Rimvis, i got it working using #Result dynamic table