By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,678 Members | 2,192 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,678 IT Pros & Developers. It's quick & easy.

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

P: n/a
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)
Jul 21 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

"droope" <dr****@discussions.microsoft.com> wrote in message
news:9F**********************************@microsof t.com...
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)

Jul 21 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.