avoid dynamic sql with zero or more params
-
I run into this all the time and I am sure, or I hope, that there is an easier way.
I produce web apps and frequently there will be a table list view that the client wants to be able to filter. Typically there are 4 or more fields they want to filter on. So for my proc I need to pass 4 params that may be null or contain a value.
Here is the question the best I can word it:
"How can I write a single proc to handle it so that if a param is NULL there is no constraint and if it is not NULL to enforce the constraint, without using dynamic SQL or multiple statements with conditional branching?"
The dynamic sql method involves checking each param and formating up any WHERE clauses in need as strings and then adding them to the base statement to execute.
The conditional branching method means writting a statement for each possible combination of params.
(e.g 4 params = 16 statements.)
In my current problem there are 5 params so the branching method would be 25 statements Yuck!
Just in case I have not described the problem clearly enough here is an example. Lets say there are only two params. @p1, @p2 that may or may not contain a value.
Branching method would need four possible statements:
both params null: "select value from table"
p2 null: "select value from table where p1=@p1"
p1 null: "select value from table where p2=@p2"
neither null: "select value from table where p1=@p1 and p2=@p2"
The dynamic SQL method is just as ugly.
Isn't there any easier way?
thx
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
select value from table
where
p1 = case when @p1 is null then p1 else @p1 end and
p2 = case when @p2 is null then p2 else @p2 end
if the columns are nullable
select value from table
where
coalesce(p1, '') = case when @p1 is null then coalesce(p1, '') else @p1 end and
coalesce(p2, '') = case when @p2 is null then coalesce(p2, '') else @p2 end
Regards,
gova -
Beautiful!
I use case statements but I hadn't thought of using them that way, equal to itself...I can hardly wait to see if it works as expected!
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
select value1,value2,value3 from table t
where (t.value = @param1 or @param1 is null)
t.value is the column which matches your searc criteria
- but Govinnlooks like this approach will use index scan not index seek.if table is huge results might take considerable time. does not it?
-
Why not use some code
If @p1 is Null Select * from Table
Else Select * from Table where c1=@p1
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
Can you imagine the code if you have 15 nullable parameters?
-
You should probably do it the other way round (as someone pointed out to me eariler on SSC
)select value1,value2,value3 from table t
where (@param1 is null or t.value = @param1)
This way, t.value = @param1 will not be evaluated if @param1 is null.
-
I hear ya... But, you write the code once... your run it a bazillion times.. having SQL use the proper indicies can cover for a little code bloat.
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
Sure thing, 15 nullable parameters = 32768 if/else combinaisons. Can you make sure that everything's gonna work fine with that much bloating?? How about when you need to add 2 more parameters??
-
You a funny guy
There is a practical limit to using this technique, I leave it up to you to make that call...
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
Yup its case dependent. For my current project there are only 5 params but there is not much load. (small tables few users). In another project of mine there are millions of rows and high traffic. I have to be much more concerned about performance and the client is more willing to pay for the extra time to write bloater code.
Thx all, That link "Dynamic Search Conditions in T-SQL" will help me speed through my decision next time.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply