Passing variables to the "Where" clause

Question

Passing variables to the "Where" clause

kservice

SSC Enthusiast

Points: 116
More actions
December 16, 2002 at 5:29 pm

#80555

I need to pass in one or more variables and return the results depending upon what was passed to the stored procedure.
For example if I pass the variable ProoferLogID, I only need the one row that matches it. The same for the MinutesLogID.
If I pass in the CommitteID I need to bring back the committees that have not had a proofer assigned to them "AssignedProoferID = 0" for that committee or if the logged in user is also the "assigned proofer", they need to see the committee minutes they are working on. If there are different proofs of the committee minutes, I only want to bring back the latest proof which I get by using ProofNumber.
I am able to get part of what I need, but it seems to blow up when I try and get the Committees and the Assigned Proofers. If I use "and" for fldAssignedProoferID , I get nothing and if I use "or" no matter what I pass in I get the committees that have not had a Proofer Assigned.
I cannot seem to be able to return exactly the information I need. I am not sure if I should be using dynamic SQL, ISNull, COALESCE, and or "or".
Thanks!
WHERE
pl.fldProoferLogID = ISNULL (@fldProoferLogID, pl.fldProoferLogID)
or
ml.fldMinutesLogID = ISNULL(@fldMinutesLogID, ml.fldMinutesLogID)
or
ml.fldCommitteeID = ISNULL(@fldCommitteeID, ml.fldCommitteeID) AND (fldProofNumber = (select max(fldProofNumber) from tblMinutesLog_Proofer)) AND (fldAssignedProoferID = fldAssignedProoferID) or (fldAssignedProoferID = 0)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 1

To be honest, I think I'd write my procs for each particular dataset returned, and use the one you have to call the one needed. Sure, its a couple new procs, but the returns outweigh the cost in re-usability, and the Procs themselves become more modular allowing changes to one function or another without impacting the others in the set, and its much easier to work with the execution plans for them.

Oh, and your actual issue looks like it might could be solved by a set of paren's placed around your conditions and a slight change to the structure. I'm not sure as I'm assuming the first two are needed regardless, but I think this might be what you want.

WHERE

pl.fldProoferLogID = ISNULL (@fldProoferLogID, pl.fldProoferLogID)

or

ml.fldMinutesLogID = ISNULL(@fldMinutesLogID, ml.fldMinutesLogID)

or

(

ml.fldCommitteeID = ISNULL(@fldCommitteeID, ml.fldCommitteeID) AND (fldProofNumber = (select max(fldProofNumber) from tblMinutesLog_Proofer)) AND (fldAssignedProoferID = fldAssignedProoferID)

)

OR

(

ml.fldCommitteeID = ISNULL(@fldCommitteeID, ml.fldCommitteeID) AND (fldProofNumber = (select max(fldProofNumber) from tblMinutesLog_Proofer)) AND (fldAssignedProoferID = 0)

)

If not, explain the different sets you want to be able to return giving details on all the variables, and if not me, then someone will help.

Edited by - scorpion_66 on 12/16/2002 5:58:28 PM

tcartwright SSCertifiable Points: 5573 More actions · Answer 2

You can accomplish what you want to do with some careful short circuiting in your where clause, and using grouping. EX:

SELECT * FROM TABLE

WHERE (@fldProoferLogID IS NOT NULL AND pl.fldProoferLogID = @fldProoferLogID)

OR (@fldMinutesLogID IS NOT NULL AND ml.fldMinutesLogID = @fldMinutesLogID)

The order and grouping plays a major role in how your where clauses work. For a better explanation of short circuiting see :

http://qa.sqlservercentral.com/faq/viewfaqanswer.asp?categoryid=2&faqid=360

OR

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9148

Tim C.

//Will write code for food

Tim C //Will code for food

kservice SSC Enthusiast Points: 116 More actions · Answer 3

I appreciate the comments from both scorpion_66 and tcartwright. I managed to get the procedure working by using if statements, it is not elegant but it worked. I am going to play with the short circuiting. If nothing else I will learn a lot.

Thanks again.

sqlemail@yahoo.com SSC Eights! Points: 919 More actions · Answer 4

I would always like to use the SQL as

Declare @SQL varchar(8000)

select @SQL = 'Select... From .... Where 1=1'

Now Start concatenating your criteria to @SQL

select @SQL = @SQL + ...

The important thing I'm highlighting is

"Where 1=1"

paul