alternative to coalesce?

Question

alternative to coalesce?

Mindy Hreczuck

SSCommitted

Points: 1796
More actions
February 2, 2011 at 8:23 am

#388764

I am writing a stored procedure with optional variables, but am not sure the best way to represent if a variable is filled in or not without repeating the SQL with and without each parameter.
I can't use Coalesce because my system's database doesn't allow NULL on the columns in question. Our applications default the columns to a blank value if data is not provided. So I need to query matching the variable to the criteria the the stored proc receives, or if none is received to disregard that column in the where clause, basically. Is that doable?
October 13, 2022 at 10:08 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Coalesce or IsNull can still work, since the variable/parameter will have no value assigned.

The problem with this method, or similar ones, is that it tends to kill performance because of non-SARGability of the query.

Dynamic SQL is often the best answer for this.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

HowardW One Orange Chip Points: 29109 More actions · Answer 2

Have a look here for more information:

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

In particular, look at the way that dynamic SQL is used but it's still parameterised (avoiding the risk of SQL Injection).

Alternatively, I believe that SQL 2008 SP2 finally contains the fix for static parameter evaluation if you add a RECOMPILE hint to the query, then you can use something along the lines of:

column = @Variable or @Variable is NULL without compromising performance, which is a more elegant solution.

EDIT:

Updated with link to more information:

http://www.sommarskog.se/dyn-search-2008.html#SPandCUs

grahamc SSCertifiable Points: 6762 More actions · Answer 3

In the past, I have used a combination of IF statements to EXECUTE other procedures with the correct SQL statement (based on usage and search criteria) for specific statements (each stored in a separate procedure) and a dynamic SQL procedure as the final catch all remaining requests.

I prefer the specific statements (not dynamic SQL) as they are relatively easy to change and tweak, where as dynamic SQL can be a bit of pain.

The dynamic SQL procedure would normally account for 5-10% of the traffic and the remainder going to the specific procedures. If the dynamic statement started to grow above that, it was time to investigate and perhaps and a little more to the controlling IF statement.

Mindy Hreczuck SSCommitted Points: 1796 More actions · Answer 4

Thanks Howard, the dynamic SQL from that blog is just what I needed.

dotedebonair Newbie Points: 3 More actions · Answer 5

When there is no data to fill out the columns in our apps, they are left as they are. It is imperative that I either match the variable to the stored procedure's arguments or omit it if no parameters were received. Do you think that is possible?

quordle