dynamic query

  • 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

  • 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.

  • 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


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 3 posts - 1 through 2 (of 2 total)

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