Passing Column Names in Stored Procedures.
-
Hi: I have this challenge that i am sure many of us would have faced. I want to write a stored procedure that has to list out all company names based on whether one of the characteristic has value 1.. For eg: create procedure (proc_name) @paramcharacteristic varchar(40) as select * from tblcompany where @paramCharacteristic=1 Now this @paramcharacteristic will always match one of the column names in the table like Performance,Satisfaction,Profits and so on.But this does not seem to work. When i run the stored procedure from an application i get the error Cannot convert varchar 'Satisfaction' to integer.So i think what it does is try to compare the value of @paramcharacteristic variable and 1.But instead i want SQL to understand the statement as select * from tblcompany where Satisfaction=1 Where do u think should i make the change.In the definition of paramcharacteristic in the stored procedure??Pls give me some suggestions.On how to deal this situation.. Thanks in advance for the help.. Regards Pradhip.S If a Building is Completed then why do they call it BUILDING ??
-
Hi: I have this challenge that i am sure many of us would have faced. I want to write a stored procedure that has to list out all company names based on whether one of the characteristic has value 1.. For eg: create procedure (proc_name) @paramcharacteristic varchar(40) as select * from tblcompany where @paramCharacteristic=1 Now this @paramcharacteristic will always match one of the column names in the table like Performance,Satisfaction,Profits and so on.But this does not seem to work. When i run the stored procedure from an application i get the error Cannot convert varchar 'Satisfaction' to integer.So i think what it does is try to compare the value of @paramcharacteristic variable and 1.But instead i want SQL to understand the statement as select * from tblcompany where Satisfaction=1 Where do u think should i make the change.In the definition of paramcharacteristic in the stored procedure??Pls give me some suggestions.On how to deal this situation.. Thanks in advance for the help.. Regards Pradhip.S If a Building is Completed then why do they call it BUILDING ??
One option is to build up the T-SQL statement dynamically, like:
exec('select * from sysobjects where ' + @myColumnName + ' >5')
Just watch out for SQL injection problems. Cheers, Simon "The day I swan around in expensive suits is the day I hope someone puts a bullet in my head.", Chris Carter.
my svg article -
One option is to build up the T-SQL statement dynamically, like:
exec('select * from sysobjects where ' + @myColumnName + ' >5')
Just watch out for SQL injection problems. Cheers, Simon "The day I swan around in expensive suits is the day I hope someone puts a bullet in my head.", Chris Carter.
my svg articleYou just saved me at least 3 days work. Cheers mate! :-D