By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,574 Members | 1,977 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,574 IT Pros & Developers. It's quick & easy.

passing a column name to a stored procedure

P: 28
Good Day,
I'm Trying to pass parameters to represent a column name and value to a stored procedure to execute but havinfg difficulties... can anyone help?

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. declare @columnName as nvarchar(30)
  4. declare @value as nvarchar(30)
  5. declare @condition as nvarchar(10)
  6.  
  7. set @columnName = 'last_name'
  8. set @type = 'LIKE'
  9. set @condition = '%g%'
  10.  
  11.  
  12.  
  13.     -- Create a variable @SQLStatement
  14.     DECLARE @SQLStatement varchar(255)
  15.  
  16.     SELECT @SQLStatement = 'select * from Users where ' + @columnName + ' ' + @type + ' ' + '' + @value + ''
  17.     -- Execute the SQL statement
  18.     EXEC(@SQLStatement)
  19.  
  20.  
  21.  
Oct 31 '08 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
First your @type variable is not declared.


Second, you did this: set @condition = '%g%'
but did this:

SELECT @SQLStatement = 'select * from Users where ' + @columnName + ' ' + @type + ' ' + '' + @value + ''

You use @value instead of @condition.

I modified it a bit.

Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. declare @columnName as nvarchar(30)
  3. declare @value as nvarchar(30)
  4. declare @condition as nvarchar(10)
  5. declare @type varchar(40)
  6.  
  7.  
  8. set @columnName = 'name'
  9. set @type = 'LIKE'
  10. set @value = '%sys%'
  11.  
  12.  
  13.  
  14. -- Create a variable @SQLStatement
  15. DECLARE @SQLStatement varchar(255)
  16.  
  17. SELECT @SQLStatement = 'select top 5 id, name from sysobjects where ' + @columnName + ' ' + @type + ' ' + '''' + @value + ''''
  18. -- Execute the SQL statement
  19. select (@SQLStatement)
  20.  
  21. exec(@SQLStatement)
  22.  
  23.  
Happy Coding!

-- CK
Oct 31 '08 #2

P: 28
Thanks Much.... I was trying so many variations thats why some variables not decared... but thanks again for the help :)





First your @type variable is not declared.


Second, you did this: set @condition = '%g%'
but did this:

SELECT @SQLStatement = 'select * from Users where ' + @columnName + ' ' + @type + ' ' + '' + @value + ''

You use @value instead of @condition.

I modified it a bit.

Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. declare @columnName as nvarchar(30)
  3. declare @value as nvarchar(30)
  4. declare @condition as nvarchar(10)
  5. declare @type varchar(40)
  6.  
  7.  
  8. set @columnName = 'name'
  9. set @type = 'LIKE'
  10. set @value = '%sys%'
  11.  
  12.  
  13.  
  14. -- Create a variable @SQLStatement
  15. DECLARE @SQLStatement varchar(255)
  16.  
  17. SELECT @SQLStatement = 'select top 5 id, name from sysobjects where ' + @columnName + ' ' + @type + ' ' + '''' + @value + ''''
  18. -- Execute the SQL statement
  19. select (@SQLStatement)
  20.  
  21. exec(@SQLStatement)
  22.  
  23.  
Happy Coding!

-- CK
Nov 8 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.