I see surprisingly often people using dynamic SQL when their need is to filter data with WHERE clause, with varying fields fields used. Say I have a table
CREATE TABLE [dbo].[SAMPLETABLE](
[id] [int] IDENTITY(1,1) NOT NULL,
[somenumber] [int] NOT NULL,
[sometext] [varchar](255) NULL,
CONSTRAINT [PK_SAMPLETABLE] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SAMPLETABLE] ON [dbo].[SAMPLETABLE]
(
[somenumber] ASC,
[sometext] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
With data something like:
1 10 Test text 1
2 11 Test text 2
3 15 Test text 2
4 12 Test text 3
5 19 Something totally different
Ok. Let's say that my query would need to filter either with minimum somenumber or LIKE sometext or with both of them (let's forget the direct id restriction for the sake of the example). The needed SQL takes the minimum allowed somenumber in, and uses LIKE for sometext.
People very often would write the SQL in .NET code. The pseudo could be
Dim sql as String="select * from dbo.sampletable"
Dim sqlwhere As String=""
if somenumber <> 0 then
sqlwhere &= "WHERE somenumber >= " & somenumber
end if
if sometext <> "" then
If sqlwhere <> "" THEN
sqlwhere &= " AND "
else
sqlwhere &= " WHERE "
end if
sqlwhere &= " sometext LIKE '%" & sometext & "%'"
end if
Then concatenate sql and sqlwhere if sqlwhere <> "" or they would build in in the stored proc and use sp_executesql or EXEC . Ofr course I didn't use parameters either in the previous pseudo, which is bad practise alone.
In fact, it's useful to know that you can build this "if something is passed then do something" directly into SQL. You can use basic boolean trick for that, by controlling the evaluation of OR operator. When either of the OR's two expressions are true, that's enough to evaluate the entire it as true. So, using certain defaults in stored proc for parameters and OR operator you can create a procedure (or just query) like this
CREATE PROCEDURE sampleTableSelect
@somenumbermin int=NULL,
@sometext varchar(255)=NULL
AS
BEGIN
SELECT * FROM dbo.SAMPLETABLE
WHERE (@somenumbermin IS NULL OR somenumber >= @somenumbermin)
AND (@sometext IS NULL OR sometext LIKE '%' + @sometext + '%')
END
As you can see now: if @somenumbermin is not passed, it has NULL as default value and the other side of the first OR isn't evaluated, meaning that part of the WHERE has no effect since it evaluates to true. Same applies for the @sometext. Basic idea is to check if passed parameter has default value , and when *not* then evaluate the other expression. Otherwise evaluate to true by default.
So this means you can execute the proc with following variations
> exec sampleTableSelect
1 10 Test text 1
2 11 Test text 2
4 12 Test text 3
3 15 Test text 2
5 19 Something totally different
> exec sampleTableSelect @sometext = 'text 2'
2 11 Test text 2
3 15 Test text 2
> exec sampleTableSelect @somenumbermin = 11
2 11 Test text 2
4 12 Test text 3
3 15 Test text 2
5 19 Something totally different
> exec sampleTableSelect @somenumbermin = 15,@sometext = 't'
3 15 Test text 2
5 19 Something totally different
And as you understand, from usage perspective be it directly running the proc or using it with SqlCommand, idea is to control it by passing the parameter. If you don't want the condition to apply, do not pass the related parameter. With this knowledge you could build queries so that you prepare for certain fields becoming queryable, and kind of "map" the likely variations in beforehand.
I'm not saying this is the correct way. It's just one way to do these things, but I like because it's explicit and keeps my procedure in control of the querying. So I prefer it.
Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!