Parameterized SQL with IN Clause

[Asif]

Hi all,

I am working on a web site using SQL Server 2005 and ASP.NET using
VB.NET.

I am running the following SQL:

Dim oReader As SqlDataReader
Dim IDs As String = "1,2,3"
Dim strSQL As String = "SELECT * FROM Author WHERE ID IN (@IDs)"
' ID field is Primary Key in Author Table

Dim objCmd As New SqlCommand
objCmd.CommandText = strSQL
objCmd.CommandType = CommandType.Text
objCmd.Parameters.Add(New SqlParameter("@TITLE", IDs)
objCmd.Connection = MyConnection
oReader = objCmd.ExecuteReader(CommandBehavior.CloseConnection)
if oReader.HasRows() Then
Do While oReader.Read
MessageBox.Show(oReader.Item(1))
Loop
End If

Above code executes without any error but with no resutls.

Can any one tell me about how to use IN Clause in Parameterized SQL.

Thanks

 

[Guest]

Hi all,

I am working on a web site using SQL Server 2005 and ASP.NET using
VB.NET.

I am running the following SQL:

Dim oReader As SqlDataReader
Dim IDs As String = "1,2,3"
Dim strSQL As String = "SELECT * FROM Author WHERE ID IN (@IDs)"
' ID field is Primary Key in Author Table

Dim objCmd As New SqlCommand
objCmd.CommandText = strSQL
objCmd.CommandType = CommandType.Text
objCmd.Parameters.Add(New SqlParameter("@TITLE", IDs)
objCmd.Connection = MyConnection
oReader = objCmd.ExecuteReader(CommandBehavior.CloseConnection)
if oReader.HasRows() Then
Do While oReader.Read
MessageBox.Show(oReader.Item(1))
Loop
End If

Above code executes without any error but with no resutls.

Can any one tell me about how to use IN Clause in Parameterized SQL.

Thanks

you can use it with IN clause

change you code to something like this

Dim strSQL As String = "SELECT * FROM Author WHERE ID IN (" + IDs +
")"

and get rid of SqlParameter

 

[Guest]

you can use it with IN clause

change you code to something like this

Dim strSQL As String = "SELECT * FROM Author WHERE ID IN (" + IDs +
")"

and get rid of SqlParameter- Hide quoted text -

- Show quoted text -

oh, sorry, a typo: you canNOT use it with IN clause

 

[Eliyahu Goldin]

The reason is because the value of @IDs is considered as one element in the
IN list. It is possible to make the IN list in run-time in form

WHERE ID IN (@id1, @id2 ...)"

--
Eliyahu Goldin,
Software Developer
Microsoft MVP [ASP.NET]
http://msmvps.com/blogs/egoldin
http://usableasp.net

 

[Cowboy \(Gregory A. Beamer\)]

You cannot do it the way you are attempting to do it. There are a couple of
ways around this.

The first is the one shown in your discussion with Alexy, where you
concatenate the ids string to the SQL string. This is fairly simple, but
cannot be done with a single @ids parameter, as you are not truly
parameterizing at this time. NOTE: If you adopt this method, you have the
potential of SQL injection.

Another method is to pass XML into SQL Server, with each ID in a separate
element. You can then join to the XML in a stored procedure and return the
results. This is similar to the IN, but immune from SQL injection.

 

[Mark Rae [MVP]]

Another method is to pass XML into SQL Server, with each ID in a separate
element. You can then join to the XML in a stored procedure and return the
results. This is similar to the IN, but immune from SQL injection.

There's a really good article on that here:
http://www.codeproject.com/sqlrs/PassingArraysSQLParameter.asp

 

[Lloyd Sheen]

There's a really good article on that here:
http://www.codeproject.com/sqlrs/PassingArraysSQLParameter.asp

I have been following this thread and one thing that has not been mentioned
is that if the method used depends on dynamically creating the SQL then you
must ensure that characters that will break the SQL are not inserted. While
this is not as bad as a SQL Injection I am sure your users don't want to see
a msg about a SQL syntax error that they unwittingly created.

LS

 

[Mark Rae [MVP]]

I have been following this thread and one thing that has not been
mentioned is that if the method used depends on dynamically creating the
SQL then you must ensure that characters that will break the SQL are not
inserted.

That's true, but doesn't apply to the native XML datatype method...

 

[MDoyle]

That's true, but doesn't apply to the native XML datatype method...

I had this question months ago, and here is an article I came across
that helped me a lot:
http://www.sommarskog.se/dynamic_sql.html

It shows how to do (among other things) exactly what you are trying to
accomplish.

 

[mark4asp]

Hi all,

I am working on a web site using SQL Server 2005 and ASP.NET using
VB.NET.

I am running the following SQL:

Dim oReader As SqlDataReader
Dim IDs As String = "1,2,3"
Dim strSQL As String = "SELECT * FROM Author WHERE ID IN (@IDs)"
' ID field is Primary Key in Author Table

Dim objCmd As New SqlCommand
objCmd.CommandText = strSQL
objCmd.CommandType = CommandType.Text
objCmd.Parameters.Add(New SqlParameter("@TITLE", IDs)
objCmd.Connection = MyConnection
oReader = objCmd.ExecuteReader(CommandBehavior.CloseConnection)
if oReader.HasRows() Then
Do While oReader.Read
MessageBox.Show(oReader.Item(1))
Loop
End If

Above code executes without any error but with no resutls.

Can any one tell me about how to use IN Clause in Parameterized SQL.

Thanks

I use an IN clause with a parameterized query in a similar scenario to
the one you want to use. However I get the results for my in clause
from a function returning a single column. I include it below. This
takes a string of integer comma separated values as input and returns
a table with a single column.

Here is a snippet of SQL using this function:

ConsultantID In (Select * From
dbo.CSV_IntegersToSingleColumn(@ConsultantIDs))


-- PS: Homage to the guy who wrote this. Sorry I lost the Url where it
came from.
-- Convert string of integer comma separated values (input) to a table
of integers (return value).
ALTER FUNCTION [dbo].[CSV_IntegersToSingleColumn]
(
@cslist VARCHAR(8000)
)
RETURNS @t TABLE
(
Item VARCHAR(64)
)
BEGIN
DECLARE @spot SMALLINT, @str VARCHAR(8000), @sql VARCHAR(8000)

WHILE @cslist <> ''
BEGIN
SET @spot = CHARINDEX(',', @cslist)
IF @spot > 0
BEGIN
SET @str = LEFT(@cslist, @spot - 1)
SET @cslist = RIGHT(@cslist, LEN(@cslist) - @spot)
END
ELSE
BEGIN
SET @str = @cslist
SET @cslist = ''
END
INSERT @t SELECT @str
END
RETURN
END

 

[Mark Rae [MVP]]

ConsultantID In (Select * From
dbo.CSV_IntegersToSingleColumn(@ConsultantIDs))

What happens if you want *all* records irrespective of ConsultantID...?

 

[mark4asp]

Dim strSQL As String = "SELECT * FROM Author WHERE ID IN (@IDs)"
...
Can any one tell me about how to use IN Clause in Parameterized SQL.

-- PS: Homage to the guy who wrote this. Sorry I lost the Url where it
came from.
-- Convert string of integer comma separated values (input) to a table
of integers (return value).
ALTER FUNCTION [dbo].[CSV_IntegersToSingleColumn]
(
@cslist VARCHAR(8000)
)
RETURNS @t TABLE
(
Item VARCHAR(64)
)
BEGIN
DECLARE @spot SMALLINT, @str VARCHAR(8000), @sql VARCHAR(8000)

WHILE @cslist <> ''
BEGIN
SET @spot = CHARINDEX(',', @cslist)
IF @spot > 0
BEGIN
SET @str = LEFT(@cslist, @spot - 1)
SET @cslist = RIGHT(@cslist, LEN(@cslist) - @spot)
END
ELSE
BEGIN
SET @str = @cslist
SET @cslist = ''
END
INSERT @t SELECT @str
END
RETURN
END- Hide quoted text -

- Show quoted text -

This will be your query:

SELECT * FROM Author WHERE ID IN
(Select * From dbo.CSV_IntegersToSingleColumn(@IDs))

 

[mark4asp]

What happens if you want *all* records irrespective of ConsultantID...?

Mark, I don't understand your question. Is the question a reference to
the
snippet of code I wrote. I just wrote something to put the function
in context.
I should have read the OPs first post (again) than I would've given
him the
query he wanted.

The function just converts a string of CSV integers to a column so
that it
can be used in a subquery (or even a JOIN). It works with SQL Server
2005
but maybe not with 2000. It depends on whether 2000 allows functions
to return a table. I suspect it doesn't. Play around with it. I've
never had
a problem with it in practice.

 

[Mark Rae [MVP]]

Mark, I don't understand your question.

Supposing you have a stored procedure which takes the "array" parameter -
it's impossible to nullify this without writing dynamic SQL in the SP...

E.g. supposing you have a table with 1,000 rows, and the second column is an
integer called StatusID.

You write a stored procedure with an "array" parameter containing one or
more StatusID values in a CSV string.

1,9,13 returns all the rows where the StatusID is 1, 9 or 13 - obviously.

But what if you want *all* the rows? You can't pass a NULL value into the
"array" parameter without writing some dynamic SQL in the stored
procedure...

You could pass in an array containing all the StatusID values, I suppose,
but that would be extremely inefficient...

 

[Anthony Jones]

Mark, I don't understand your question. Is the question a reference to
the
snippet of code I wrote. I just wrote something to put the function
in context.
I should have read the OPs first post (again) than I would've given
him the
query he wanted.

The function just converts a string of CSV integers to a column so
that it
can be used in a subquery (or even a JOIN). It works with SQL Server
2005
but maybe not with 2000. It depends on whether 2000 allows functions
to return a table. I suspect it doesn't. Play around with it. I've
never had
a problem with it in practice.

That function works fine in SQL 2000. Personally I prefer the XML approach
since it has wider application but XML is slower and awkward under 2000.

 

[Mark Rae [MVP]]

Personally I prefer the XML approach since it has wider application

Me too...