VB.net, StoredProcedures and Parameter weirdo

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi

When Calling a StoredProc, with a Parameter, where the parameter is a list
applied against an "IN" clause,
eg


In Code, ... mycommand.Parameters("@sP1_Id").Value = " 'A','B' "

StoredProc xxxx (@sP1_Id as varchar 32)
....
...Select..
...From
...Where SomeField IN (@sP1_Id)
....
When i run the app
Nothing gets returned..??
Debuging shows the value of @sP1_Id as it enters the StoredProc as 'A','B'

and if I plug them straight in ..ie
...WHERE SomeField IN ('A','B') then it works fine.

Trying "A,B" definitely won't ... and doesn't work.

SO somehow the parameter, is not being seen correctly...the single quotes
and/or comma may be reponsible as... if I only use one item in the IN clauses
List, it works fine.

Is there a way to do this or must I find a work around.
TIA
Neal
 
Parameters are cast as a specific type. If you want to do an IN ('A','B') you
have two choices.

1. Dump the values in a temp table and join on that table:

CREATE TABLE #mytemp
(
val char(1)
)

-- then insert here (have to rip out values - downside)

-- then select
SELECT t1.* FROM Table1
JOIN #mytemp ON t1.val = t2.val

That effectively does your in and allows SQL Server to optimize the query. I
would consider switching the input to XML, as the FOR_XML is easier to break
out values, but you can leave as a String, if you desire.

2. Dynamically build the query and use EXEC. This is easier, as you can
build a command like:

SET @cmd = 'SELECT t1.* FROM Table1 WHERE t1.val IN (' + @sP1_id + ')'

EXEC @cmd

The downside here is you lose the ability to completely compile the
statement before running and can slow down the system. The upside is it is
very easy to dynamically build the query.

Hope this makes sense.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
Back
Top