Concatenate string & variable to build column name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Concatenate string & variable to build column name

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2012-10-03 : 12:22:32

Hi,

I wish to build a stored procedure with variables.
Ex:Select vmLimit - vmProvisioned as AvailableVM from table1 would become in my SP:

Declare @ComponentName NvarChar(12)
Set @ComponentName = 'vm'
Select (@ComponentName + '' 'Limit') - vmProvisionned As AvailableVM
From table1

I receive the following error.
Conversion failed when converting the varchar value 'vmLimit' to data type int.

Any Idea!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-03 : 12:25:18

do you mean this? otherwise it doesnt make any sense


Declare @ComponentName NvarChar(12)
Set @ComponentName = 'vm'
EXEC('Select ' + @ComponentName + 'Limit - vmProvisionned As AvailableVM From table1' )

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

infodemers
Posting Yak Master

183 Posts

Posted - 2012-10-03 : 12:28:12

Hi Visakh16,

That is exactly what I meant to say and again, you have the solution for me!

Thanks a lot! :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-03 : 12:34:20

welcome
please be aware of fact that this method is prone to SQL Injection attack and make sure you add required validations from that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

infodemers
Posting Yak Master

183 Posts

Posted - 2012-10-03 : 13:29:22

Hi visakh16,

Doing what you said,can you see what is wrong with the following query?

DECLARE @ComponentName NvarChar(12);
DECLARE @Threshold1 NvarChar(12);
DECLARE @Threshold2 NvarChar(12);
DECLARE @ok as CHAR(3);

SET @ComponentName = 'vm';
SET @Threshold1 = 'Yellow';
SET @Threshold2 = 'Red';
SET @ok = 'N/A';

exec('SELECT tbl_Complex.ComplexName, tbl_Component.ComponentName, tbl_Management.Treshold1,
tbl_Management.Treshold2,
Case
When ' + @ComponentName + 'Limit - vmProvisionedInUse <= tbl_Management.Treshold1 and ' + @ComponentName + 'Limit - tbl_Data1.vmProvisionedInUse > tbl_Management.Treshold2
Then ' + @Threshold1 + '
When ' + @ComponentName + 'Limit - tbl_Data1.vmProvisionedInUse <= tbl_Management.Treshold2
THEN ' + @Threshold2 + '
else ' + @ok + '
End as AvailableVM,
tbl_Data1.[Date]
FROM tbl_Complex INNER JOIN
tbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOIN
tbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOIN
tbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentID
where tbl_Component.ComponentName = ' + @ComponentName + '
order by tbl_Complex.ComplexName, tbl_Component.ComponentName, tbl_Data1.[Date]')

I receive the following errors:
Invalid column name 'vm'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'Yellow'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Red'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'N'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'A'.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-03 : 13:31:13

replace exec with print and post the result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-03 : 13:32:49

one guess is this?


exec('SELECT tbl_Complex.ComplexName, tbl_Component.ComponentName, tbl_Management.Treshold1, 
tbl_Management.Treshold2,
Case 
When ' + @ComponentName + 'Limit - vmProvisionedInUse <= tbl_Management.Treshold1 and ' + @ComponentName + 'Limit - tbl_Data1.vmProvisionedInUse > tbl_Management.Treshold2
Then ''' + @Threshold1 + ''' 
When ' + @ComponentName + 'Limit - tbl_Data1.vmProvisionedInUse <= tbl_Management.Treshold2
THEN ''' + @Threshold2 + '''
else ''' + @ok + '''
End as AvailableVM, 
tbl_Data1.[Date]
FROM tbl_Complex INNER JOIN
tbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOIN
tbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOIN
tbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentID
where tbl_Component.ComponentName = ' + @ComponentName + '
order by tbl_Complex.ComplexName, tbl_Component.ComponentName, tbl_Data1.[Date]')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

infodemers
Posting Yak Master

183 Posts

Posted - 2012-10-03 : 13:34:50

Here it is:
The value vm, Yellow, Red and N/A does not appear between single quote in the query??

SELECT tbl_Complex.ComplexName, tbl_Component.ComponentName, tbl_Management.Treshold1, 
tbl_Management.Treshold2,
Case 
When vmLimit - vmProvisionedInUse <= tbl_Management.Treshold1 and vmLimit - tbl_Data1.vmProvisionedInUse > tbl_Management.Treshold2
Then Yellow 
When vmLimit - tbl_Data1.vmProvisionedInUse <= tbl_Management.Treshold2
THEN Red 
else N/A
End as AvailableVM, 
tbl_Data1.[Date]
FROM tbl_Complex INNER JOIN
tbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOIN
tbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOIN
tbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentID
where tbl_Component.ComponentName = vm
order by tbl_Complex.ComplexName, tbl_Component.ComponentName, tbl_Data1.[Date]

infodemers
Posting Yak Master

183 Posts

Posted - 2012-10-03 : 13:37:48

Hi visakh16,

You are very strong!
It now works like a charm.

It is a chance we have people like you in this world to help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-03 : 14:06:59

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources