I found a solution on Google. Here's the url: http://www.sqlteam.com/item.asp?ItemID=2077
There's a function called COALESCE which picks the first non-null value in a comma delimeted list. Check out the examples in the above link on how to apply this for dynamic WHERE clauses. My biggest hangup to using stored procs has been the inability to create dynamic WHERE clauses, so now I'll start incorporating them into my development. Has anyone used COALESCE function as described above? Will this degrade performance? I prefer not to have several different stored procs versus putting the logic into 1 file. Does anyone know the performance gains from stored procs versus using a CF query? Thanks for your help - Tom -----Original Message----- From: Hinojosa, Robert A [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 19, 2001 1:28 PM To: '[EMAIL PROTECTED]' Subject: RE: stored procs 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 ------------------------------------------------------------------------- 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
