Passing a parm to a stored proc that has an "in clause"

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

Guest

I am trying to create a proc with a parm @whereclause that will have a value
passed in of one to many policy ids. The parm coming into the proc looks
like this, ('000000000108', '000000000106', '000000000394').

Can you create a proc that accepts the in clause values as a parm?

I did get it to work using the second syntax. I would prefer to use the
first syntax if possible.


First Syntax

CREATE PROCEDURE spMTGetOESEmployerQNXTData
@WhereClause varchar(1000)
AS

--
======================================================================================================
-- Select PolicyPlans

Select rateid,
polid,
planid,
feeid,
remitid,
eobid,
effdate,
termdate,
lastupdate,
updateid,
rategroup,
createid,
createdate,
coveragecodeoption
From planprog.dbo.policyplans
Where Polid In @WhereClause

---
======================================================================================================
--

Second Syntax

Declare @query varchar (5000)

Set @query = 'Select
rateid,
polid,
planid,
feeid,
remitid,
eobid,
effdate,
termdate,
lastupdate,
updateid,
rategroup,
createid,
createdate,
coveragecodeoption
From planprog.dbo.policyplans'
+ ' Where planprog.dbo.policyplans.Polid In '
+ @WhereClause

EXEC (@query)
 
This is really a SQL Server question, not DotNet, but since I played with
this a couple months ago I'll tell you the answer:

No, you can not use the first way of doing it, no way around it either that
I could fine, and I looked.. In your example there may be an idea you could
try, but I haven't tried it at all. Create a temp table with one column,
insert your @WhereClause values into the temp table. Then use the temp table
in your where clause.

Something like.... (syntax way off)
CREATE PROCEDURE spMTGetOESEmployerQNXTData
@WhereClause varchar(1000)
AS

Create Table #TempValues {
ID Varchar 15
}

'Parse out the values and insert them in temp table

Select rateid,
polid,
planid,
feeid,
remitid,
eobid,
effdate,
termdate,
lastupdate,
updateid,
rategroup,
createid,
createdate,
coveragecodeoption
From planprog.dbo.policyplans
Where Polid In #TempValues


At least this way SQL preprosses the query unlike the second syntax you
showed, which it can't.

good luck
Chris
 
Back
Top