SQL Server Performance Forum – Threads Archive
Stored procedured – revisited – where clause
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
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)
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
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
[email protected] When life gives you a lemon, fire the DBA.
]]>