August 30, 2007 at 6:48 am
Ok, so I've inherited a bunch of dynamic sql in stored procs and have been asked to re-write using t-sql. The args to the proc are a bunch of comma delimited strings, which may also be null. I have a function, which seems to be efficient, that returns a simple table from a comma delimited string and I know of two ways to create a conditional join or where based on whether the params are null or not. My question is, which one is better...? (or, for that matter, would it be better to stay with the dynamic sql or do you have another suggestion...?) The examples below are greatly simplified...
This way uses a case statement in the where clause:
select m.field1,
m.field2,
m.field3,
m.field4,
m.address1,
m.city,
FROM master m
INNER JOIN #Filter f ON f.master_key = m.master_key
Left Outer Join table1 t ON m.master_key = t.master_key
where m.address1 LIKE isnull(@Adx1, '%')
and m.city LIKE isnull(@city, '%')
and case IsNull(@status, '')
when '' then
case when 1 = 1 then 1 end
else
case when m.status in (select value from dbo.funcStringToTable(@Status, ',')) then 1
end
end = 1
This way uses an exists statemet:
select m.field1,
m.field2,
m.field3,
m.field4,
m.address1,
m.city,
FROM master m
INNER JOIN #Filter f ON f.master_key = m.master_key
Left Outer Join table1 t ON m.master_key = t.master_key
where m.address1 LIKE isnull(@Adx1, '%')
and m.city LIKE isnull(@city, '%')
and (exists(
select * from dbo.funcStringToTable(@Status, ',') s
where IsNull(m.Status, '') = IsNull(s.value, ''))
or @status is Null)
Thanks for any input you may have!
August 30, 2007 at 9:06 am
Hello,
let me give you the standard link for your situation 🙂 it explains lots of things and gives tips on how to avoid dynamic SQL best (or how to use it properly when there is no other way) :
August 30, 2007 at 9:22 am
Thanks for the link, but in essence, we already know dynamic sql isn't the answer. The current dynamic sql is too long to reasonably maintain, somewhat poorly structured and unless there is a huge performance advantage to using dynamic sql over one of the examples above, it is going to be ported to t-sql.
The scenario of the existing proc is that there are ~50 args that could be comma delimited lists, could be null, or could be single values that may need to be used in the query. There are other things happening in the proc too that I don't have issues with porting, but the for the 50 or so args, is one of the above methods better than the other, or is there another alternative that would be better.
Thanks!
tk
August 30, 2007 at 10:06 am
Assuming I understand what you're doing - have you considered doing away with using the function at all?
use something like
....
case when (@status like '%,'+s.value+'%') or (@status like s.value+',%') then 1 else....
You're doing a string parse, to a table to do a string compare, instead of a "straight" string compare. Of course - if @status is a few thousand values long - then that's a little different, in which case your EXISTS clause would likely yield quicker results.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 30, 2007 at 12:15 pm
Yeah, I wish I could do something like that, but as an example using the status values, the input string could be something like A,AN,N,A1,N1 or it could be A,AN in any varied order or possible combination of valid values for that particular arg/col. And, in some cases the values are descriptive, which could lead to erroneous results as well. In general, the combinations in the comma delimited strings aren't that long (varchar 200), but I don't see how I could use a combination of simple like statements and not get erroneous results.
As far as I can tell, the funciton is fairly efficient given that it simply parses the string based on the delimiter passed in and returns a single col table. I guess I was looking for an easy answer as to which method (case statements or exists clauses) would be more efficient overall since they do essentially the same thing. Does one plan more efficiently when used in this way, etc...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply