Couple of thoughts (but I am be no means an expert on stored procedures): Should there be OR or AND statements between the if statements?
I read something recently where the author suggested having separate stored procedures for a situation like this and making the decision before you call the stored procedure as to which one to call. His reasoning was two fold. First it is simpler to understand and maintain (as well as simpler to develop and less error prone) and secondly the individual procedures will out perform one with a bunch of if logic in it. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Schreck, Tom 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
