• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

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
0
dev_intagleo
Asked:
dev_intagleo
  • 3
  • 3
  • 2
  • +3
1 Solution
 
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
 
Pratima PharandeCommented:
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
 
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now