You may want to consider building the sql as a string and then use exec to
execute the statement. Something like this.
SELECT @sqlstring = "SELECT * FROM <tablename> WHERE <fieldname> " +
@dynamicWhereClause + " IN (" +@parameter = ")"
EXEC(@sqlstring)
HTH,
Robert Hinojosa
[EMAIL PROTECTED]
972.243.4343 x7446
-----Original Message-----
From: Schreck, Tom [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 19, 2001 11:58 AM
To: [EMAIL PROTECTED]
Subject: stored procs
I'm trying to create a stored procedure that accepts any combination of 5
parameters and filters a recordset. I'm applying defaults to the parameters
so I know if one of the parameters matches the default then I do not include
that parameter as part of the filter. Here's a snippet:
DECLARE @numContentTypeID int,@numWorkFlowStateID int,@numTaxonomyID int,
@dtStart datetime, @dtEnd datetime
SELECT @numContentTypeID = 0
SELECT @numWorkFlowStateID = 0
SELECT @numTaxonomyID = 7
SELECT @dtStart = getDate()
SELECT @dtEnd = dateadd(month,1,getdate())
--PRINT @dtStart
--PRINT @dtEnd
--set top 3 content
SELECT TOP 3 numContentID, numHitCount
FROM tblContent
WHERE 0=0
if @numContentTypeID > 0
AND tblContent.numContentTypeID = @numContentTypeID
if @numWorkFlowStateID > 0
AND tblContent.numWorkFlowStateID = @numWorkFlowStateID
if @numTaxonomyID > 0
AND tblContent.numContentID IN(
SELECT tblTaxonomyContent.numContentID
FROM tblTaxonomyContent
WHERE (tblTaxonomyContent.numTaxonomyID =
@numTaxonomyID)
)
ORDER BY numHitCount DESC
Am I missing the syntax on how to dynamically create the WHERE clause?
Could it be that stored procedures can not create dynamic WHERE clauses? If
so, then do you have to make up a huge if statement block to try to
determine all possible permutations of 5 different parameters? If stored
procs do not allow for dynamic WHERE clauses, then this seems to be a very
serious week point for using stored procs. What are your thoughts?
Thanks - Tom
-------------------------------------------------------------------------
This email server is running an evaluation copy of the MailShield anti-
spam software. Please contact your email administrator if you have any
questions about this message. MailShield product info: www.mailshield.com
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To subscribe / unsubscribe: http://www.dfwcfug.org
-------------------------------------------------------------------------
This email server is running an evaluation copy of the MailShield anti-
spam software. Please contact your email administrator if you have any
questions about this message. MailShield product info: www.mailshield.com
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To subscribe / unsubscribe: http://www.dfwcfug.org
