hongclub
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
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
Last Comment
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
(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
ASKER
any suggestion, can't believe it is so complicated to generate a dynamic column name
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
thanks Rimvis, i got it working using #Result dynamic table
ASKER
another question, can i create dynamic table with dynamic column
What do you mean? Could you give an example please?
SQL
SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.
61K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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.
Open in new window