RE: VB.net, StoredProcedures and Parameter weirdo by NoSpamMgbworld
NoSpamMgbworld
Fri Mar 04 11:25:03 CST 2005
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!
***************************
"Neal" wrote:
> 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
>
> --
> Neal Rogers
> University of Cape Town