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
