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 AvailableVMFrom 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 senseDeclare @ComponentName NvarChar(12)Set @ComponentName = 'vm'EXEC('Select ' + @ComponentName + 'Limit - vmProvisionned As AvailableVM From table1' ) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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
|
welcomeplease be aware of fact that this method is prone to SQL Injection attack and make sure you add required validations from that------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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.Treshold2Then ' + @Threshold1 + ' When ' + @ComponentName + 'Limit - tbl_Data1.vmProvisionedInUse <= tbl_Management.Treshold2THEN ' + @Threshold2 + ' else ' + @ok + 'End as AvailableVM, tbl_Data1.[Date]FROM tbl_Complex INNER JOINtbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOINtbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOINtbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentIDwhere 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 5Invalid column name 'Yellow'.Msg 207, Level 16, State 1, Line 7Invalid column name 'Red'.Msg 207, Level 16, State 1, Line 8Invalid column name 'N'.Msg 207, Level 16, State 1, Line 8Invalid 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 MVPhttp://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.Treshold2Then ''' + @Threshold1 + ''' When ' + @ComponentName + 'Limit - tbl_Data1.vmProvisionedInUse <= tbl_Management.Treshold2THEN ''' + @Threshold2 + '''else ''' + @ok + '''End as AvailableVM, tbl_Data1.[Date]FROM tbl_Complex INNER JOINtbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOINtbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOINtbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentIDwhere tbl_Component.ComponentName = ' + @ComponentName + 'order by tbl_Complex.ComplexName, tbl_Component.ComponentName, tbl_Data1.[Date]') ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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.Treshold2Then Yellow When vmLimit - tbl_Data1.vmProvisionedInUse <= tbl_Management.Treshold2THEN Red else N/AEnd as AvailableVM, tbl_Data1.[Date]FROM tbl_Complex INNER JOINtbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOINtbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOINtbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentIDwhere tbl_Component.ComponentName = vmorder 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|