Simple SQL: Optional Parameters, Static Statements

Dynamic SQL, as wonderful a thing as it is, is horrible for security and maintenance. Your best bet is to avoid it. People often whine about making parameters optional. If you set the parameters that you are not using to null, this is a simple thing to do:

declare
@state char(02),
@zip char(05)

set @state = 'CA'
set @zip = null

select
Address
from
CustomerAddress
where
State = coalesce(@state, State)
and Zip = coalesce(@zip, Zip)

Simple and elegant.

Tags: sql