Create column name with parameters

Declare @col varchar(30)
            SET @col = '[' +  cast(@Year as varchar(20)) + '_Growth' + ']';
            UPDATE tblUnitsGrowth
            SET @col  = cast (cast(@Number as money)*100 as varchar(50) ) + '%'
            WHERE X3_Desc = @X3_Desc AND X4_Desc = @X4_Desc

i have a colum with name [2011_Growth] and i have this 2011 in @Year variable.
so i create @col which has [2011_Growth] value in it, but its not updating the value
but if i place
SET [2011_Growth]  = cast (cast(@Number as money)*100 as varchar(50) ) + '%'
directly into the query its working fine, but not with using of @col variable,

I cant avoid @col variable as i have @Year coming as parameter and based on it i decide the colum which needs to be update, how can i do that with my @col variable or some other way.

Kindly guide
dev_intagleoAsked:
Who is Participating?
 
Pratima PharandeConnect With a Mentor Commented:
Declare @col varchar(30)
            SET @col =   cast(@Year as varchar(4)) + '_Growth' ;
            UPDATE tblUnitsGrowth
            SET @col  = cast (cast(@Number as money)*100 as varchar(50) ) + '%'
            WHERE X3_Desc = @X3_Desc AND X4_Desc = @X4_Desc
0
 
mortimer452Commented:
You'll need to build the entire UPDATE statement within a string and execute with sp_ExecuteSQL, similar to below:


DECLARE @sql varchar(4000)

SET @sql = 'UPDATE tblUnitsGrowth 
SET [' + Cast(@year as varchar(20)) + '] = cast (cast(@Number as money)*100 as varchar(50)) + ''%'' 
WHERE X3_Desc = @X3_Desc AND X4_Desc = @X4_Desc'

EXEC sp_ExecuteSQL @sql

Open in new window

0
 
Mrugesh1Commented:
Declare a variable and assign query to variable and excute it....

 
Declare @col varchar(30)
Declare @query varchar(2000)
SET @col = '[' +  cast(@Year as varchar(20)) + '_Growth' + ']';
SET @query = 'UPDATE tblUnitsGrowth ' + @col  + '= cast (cast(@Number as money)*100 as varchar(50) ) + ''%'' WHERE X3_Desc = ' + @X3_Desc +' AND X4_Desc = +' @X4_Desc
print @query
exec (@query)

Open in new window

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
dqmqCommented:
don't forget the '_Growth'

                                                             
SET [' + Cast(@year as varchar(20)) + '_Growth] =  ...
                                                           
0
 
dev_intagleoAuthor Commented:
i am getting the following error while executing the sp

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
0
 
Mrugesh1Commented:
Try this solution...

Declare @col varchar(30)
Declare @query varchar(2000)
SET @col = '[' +  cast(@Year as varchar(20)) + '_Growth' + ']';
SET @query = 'UPDATE tblUnitsGrowth ' + @col  + '= cast (cast(@Number as money)*100 as varchar(50) ) + ''%'' WHERE X3_Desc = ' + @X3_Desc +' AND X4_Desc = +' @X4_Desc
print @query
exec (@query)

Open in new window

0
 
dqmqCommented:
You likely will need more quotes:

WHERE X3_Desc = ''' + @X3_Desc +''' AND X4_Desc = ''' + @X4_Desc + ''''
print @query
exec (@query)
0
 
Mrugesh1Commented:
I think @X3_Desc and @X4_Desc both will be variables like @col. If so then no need of quotes.
0
 
mortimer452Commented:
Sorry intaglio, change the @sql variable to nvarchar(4000) instead of varchar(4000), should work
0
 
mortimer452Commented:
Like this:

 
DECLARE @sql varchar(4000)

SET @sql = 'UPDATE tblUnitsGrowth 
SET [' + Cast(@year as varchar(20)) + '] = cast (cast(@Number as money)*100 as varchar(50)) + ''%'' 
WHERE X3_Desc = ''' + @X3_Desc + ''' AND X4_Desc = ''' + @X4_Desc + ''''

EXEC sp_ExecuteSQL @sql

Open in new window

0
 
GhunaimaCommented:
or change "EXEC sp_ExecuteSQL @sql" to "EXEC (@ssql)"

----- mortimer452: Sorry intaglio, change the @sql variable to nvarchar(4000) instead of varchar(4000), should work
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.