SQL Server Performance

Stored procedured - revisited - where clause

Discussion in 'T-SQL Performance Tuning for Developers' started by cbtoolkit, Jan 18, 2005.

  1. cbtoolkit New Member

    Hello all. I am back on my stored procedure question(s)...

    I have record locking properly and have you (Twan in particular) to thank. I had been asking about dynamic sql queries but now have created 8 stored procs. They all perform the same basic function but the where criteria is quite a bit different for each one. However, the variables I pass to each is the same - the differences are actually more hard-coded than dynamic..

    Here is my question:

    Can I have a single stored proc that has multiple where clauses, depending on a variable I send - not really dynamic sql but something like this...

    @intCG would be a variable passed to the stored proc - along with others. It, however, would be used to determine which where clause to use...


    Select @recno = calllist_uno from vwCallList_TZ
    WHERE iscomplete = 0 and lockdatetime is null and cancall = -1
    if intCG = 1
    and yada, yada, yada
    else

    if intCG = 2
    and yada, yada, yada


    or something similar. I am researching, is there an equivalent to a select case construct for t-sql?

    Then I could maintain a single stored proc and use other passed parameters based on the intCG variable.

    Thanks,

    Matthew Moran
    The IT Career Builder's Toolkit
    http://www.cbtoolkit.com
  2. mmarovic Active Member

    as we discussed in another thread:
    Select @recno = calllist_uno
    from vwCallList_TZ
    WHERE iscomplete = 0 and lockdatetime is null and cancall = -1
    and ((@intCG = 1 and yada, yada, yada)
    or (@intCG = 2 and yada, yada, yada))
    However, I prefer to make two stored procedures one for @intCG =1 another for @intCG=2 (without @intCG as a parameter)
  3. cbtoolkit New Member

    Thanks. I remember that method being covered. I was trying to get away from having the 8 stored procedures I've developed doing the same thing.

    There could actually be an @intCG value of 1-4 and a value for two other fields.

    Matthew Moran
    The IT Career Builder's Toolkit
    http://www.cbtoolkit.com
  4. derrickleggett New Member

    Matthew, anytime you use logic like this, you are going to be using OR logic. This logic will often need different query plans to properly arrive at a solution. Because of this, your rate of compiles is going to significantly increase. Many times the actual compilation of the stored procedure/code can take longer than the entire execution. By using function with a wrapper query, you can avoid these recompiles, have very good execution plans, and get results much faster.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.

Share This Page