Home Forums SQL Server 2005 T-SQL (SS2K5) Stored Proc - Nullable Parameters for optional filtering - handling LinkTable LEFT JOINs RE: Stored Proc - Nullable Parameters for optional filtering - handling LinkTable LEFT JOINs

  • November 19, 2012 at 10:57 am

    #1560465

    kramaswamy (11/19/2012)

    Yeah - I've used the Dynamic SQL approach before, and it does work pretty well, but I'm not a huge fan of it. Never knew about the RECOMPILE option though, I'll have to try that out.

    Still, doesn't really help with the question at hand. I mean, yes - I could use the dynamic SQL solution, and that would solve my problem. If there is no better, more elegant solution, then I suppose I'll just go with that.

    As per article, Dynamic SQL, most likely, would give you the best performance, but if you insist, there is another way to write your query:

    DECLARE @TableB_ID INT

    SELECT DISTINCT

    A.ID

    FROM TableA A

    LEFT JOIN LinkTable LT ON LT.TableA_ID = A.ID

    WHERE @TableB_ID IS NULL

    OR LT.TableB_ID = @TableB_ID

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]