1

I am trying to get a "where in" to work using a parameter in sql.

SELECT * FROM tablename WHERE(name IN (@names))

So I execute the SQL in sqlmanager, and enters 'Name1', 'Name2' in @name value, when i get asked for it. This way, query returns 0 rows. But if i change the SQL to:

SELECT * FROM tablename WHERE(name IN ('Name1','Name2'))

It works.

What is the trick to make it work?

This post solved it for me: Link to post Thanks for all the responses :)

Community
  • 1
  • 1
Thomas Petersen
  • 43
  • 1
  • 5
  • 2
    Quick quiz - name *any* programming language that, if you pass a single parameter, it will inspect the interior of that parameter, spot the comma, split on the comma, and treat everything from then on as if multiple parameters were provided? – Damien_The_Unbeliever Jun 28 '12 at 10:49

1 Answers1

0

Please Try this..

Declare @names varchar(150)
Set @names = '''Name1'',''Name2'''
Declare @str varchar(max) 
set @str = 'SELECT * FROM tablename WHERE(name IN ('+@names+'))'
Execute(@str)
Manoj Savalia
  • 1,402
  • 3
  • 13
  • 36
  • This is vulnerable to SQL injection if the names list includes [Bobby Tables](http://xkcd.com/327/) – Martin Smith Jun 28 '12 at 10:48
  • Sql Injection is not possible because here @names is Parameter in Sql stored procedure. – Manoj Savalia Jun 28 '12 at 11:13
  • 1
    Just because `@names` is a parameter doesn't magically protect you from SQL injection if you then construct a non parameterised dynamic SQL query through string concatenation and execute it. – Martin Smith Jun 28 '12 at 11:15