SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Concatenate string & variable to build column name
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

infodemers
Posting Yak Master

Canada
181 Posts

Posted - 10/03/2012 :  12:22:32  Show Profile  Reply with Quote
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
52317 Posts

Posted - 10/03/2012 :  12:25:18  Show Profile  Reply with Quote
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/

Go to Top of Page

infodemers
Posting Yak Master

Canada
181 Posts

Posted - 10/03/2012 :  12:28:12  Show Profile  Reply with Quote
Hi Visakh16,

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

Thanks a lot! :-)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/03/2012 :  12:34:20  Show Profile  Reply with Quote
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/

Go to Top of Page

infodemers
Posting Yak Master

Canada
181 Posts

Posted - 10/03/2012 :  13:29:22  Show Profile  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/03/2012 :  13:31:13  Show Profile  Reply with Quote
replace exec with print and post the result

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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/03/2012 :  13:32:49  Show Profile  Reply with Quote
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/

Go to Top of Page

infodemers
Posting Yak Master

Canada
181 Posts

Posted - 10/03/2012 :  13:34:50  Show Profile  Reply with Quote
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]
Go to Top of Page

infodemers
Posting Yak Master

Canada
181 Posts

Posted - 10/03/2012 :  13:37:48  Show Profile  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/03/2012 :  14:06:59  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000