September 8, 2010 at 9:41 am
I have a stored procedure that contains one statement that may or may not be needed depending on a variable that is passed to it.
The query looks like this:
SELECT A.sid, A.name, A.values, B.type, B.subject
FROM student A
JOIN classes B ON B.studentID = A.studentID
WHERE sid <> 0
AND B.subject IS NOT NULL
AND B.type IN (SELECT types FROM classTypes)
The last line(AND B.type IN (SELECT types FROM classTypes)) in the code above is the line that may or may not be needed. Is there way to dynamically include or exclude that line?
Thanks
September 8, 2010 at 10:05 am
Without a sample schema and data I can only make a guess at it. But here is an idea to start with:
DECLARE @btype int = NULL;
SELECT A.sid, A.name, A.values, B.type, B.subject
FROM student A
JOIN classes B ON B.studentID = A.studentID
WHERE sid <> 0
AND B.subject IS NOT NULL
AND (
(@btype IS NULL )
OR
(B.type IN (SELECT types FROM classTypes))
)
If the parameter/variable has a NULL value the IS NULL line will supercede the next one due to the OR condition. If there is a value other than NULL the condition will fall through and include your IN condition.
You may need to work with this a bit to get the parentheses arranged right, but this is where I would start.
September 8, 2010 at 10:23 am
Jerry - You might want to take a look at Gail's excellent blog[/url] on what you're proposing.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply