| Author |
 Topic  |
|
|
infodemers
Posting Yak Master
Canada
166 Posts |
 Posted - 10/03/2012 : 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
India
47040 Posts |
Posted - 10/03/2012 : 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
Canada
166 Posts |
Posted - 10/03/2012 : 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
India
47040 Posts |
Posted - 10/03/2012 : 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
Canada
166 Posts |
Posted - 10/03/2012 : 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
India
47040 Posts |
Posted - 10/03/2012 : 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
India
47040 Posts |
Posted - 10/03/2012 : 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
Canada
166 Posts |
Posted - 10/03/2012 : 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
Canada
166 Posts |
Posted - 10/03/2012 : 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
India
47040 Posts |
Posted - 10/03/2012 : 14:06:59
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Concatenate string & variable to build column name
