"WJ" <wj@nospam.com> wrote in message
news:OvUBA2fzEHA.2572@tk2msftngp13.phx.gbl...
>I need to create a proc that searches a table. However, I want to have
> conditional filtering on it.
>
> For example, if I have a person table, and the user types in a filter for
> the LastName field like 'S%'
> it will return all records where the LastName begins with S.
> However, if the user also enters Height between 5 & 6 then that will be
> added as well.
>
> So the first query will look like
>
> Select * from tblPerson where LastName like 'S%'
>
> the next query will be
>
> Select * from tblPerson where LastName like 'S%' and Height Between
> @MinHeight and @MaxHeight
>
> Here's my ordeal. If the filter is not passed, I don't want it in the
> where
> clause (e.g. the height).
> My table has over 30 fields that can be filtered on.
>
> One way to do this is to generate the SQL dynamically and then exec it.
> That works. But my concern is
> this query will be used heavily. The Dynamic SQL will not be as efficient
> as a compiled query, since
> SQL will have to generate the execution plan each time.
>
> Is there another way I can test the params passed in, and if both
> @MinHeight
> and @MaxHeight are both
> null, then omit them from the where clause?
>
> Thanks!
>
>

"David Gugick" <davidg-nospam@imceda.com> wrote in message
news:OKQ23AgzEHA.3656@TK2MSFTNGP09.phx.gbl...
>
> Dynamic SQL can reuse execution plans. You need to use sp_executesql and
> bind the parameters into the query. Assuming you get similar executions,
> the exeuction plans should be reused. The caveat being that select
> access to the underlying tables needs to be granted to the users.
>
> Given that you have 30 columns and a large number of possible executions
> (it appears you are going to allow any combination) , I would use
> dynamic SQL.
>
> It's not like queries that say "Where (col = @col_val or @col_val is
> null)" for 30 columns will be very efficient. I would be more concerned
> about users entering criteria that won't be helped by an index or that
> return too many rows. Consider using WITH (NOLOCK) on your queries to
> prevent unnecessary locking. At least, that way, a big select statement
> won't acquire shared locks on the tables.
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

WJ wrote:
> I need to create a proc that searches a table. However, I want to
> have conditional filtering on it.
>
> For example, if I have a person table, and the user types in a filter
> for the LastName field like 'S%'
> it will return all records where the LastName begins with S.
> However, if the user also enters Height between 5 & 6 then that will
> be added as well.
>
> So the first query will look like
>
> Select * from tblPerson where LastName like 'S%'
>
> the next query will be
>
> Select * from tblPerson where LastName like 'S%' and Height Between
> @MinHeight and @MaxHeight
>
> Here's my ordeal. If the filter is not passed, I don't want it in
> the where clause (e.g. the height).
> My table has over 30 fields that can be filtered on.
>
> One way to do this is to generate the SQL dynamically and then exec
> it. That works. But my concern is
> this query will be used heavily. The Dynamic SQL will not be as
> efficient as a compiled query, since
> SQL will have to generate the execution plan each time.
>
> Is there another way I can test the params passed in, and if both
> @MinHeight and @MaxHeight are both
> null, then omit them from the where clause?
>
> Thanks!

> Actually, I was going to exec the Dynamic SQL from a stored
> procedure, so I'd still
> have to only grant exec to the proc to users.

"WJ" <wj@nospam.com> wrote in message news:uX58mRgzEHA.1452@TK2MSFTNGP11.phx.gbl...
>
> "David Gugick" <davidg-nospam@imceda.com> wrote in message
> news:OKQ23AgzEHA.3656@TK2MSFTNGP09.phx.gbl...
> >
> > Dynamic SQL can reuse execution plans. You need to use sp_executesql and
> > bind the parameters into the query. Assuming you get similar executions,
> > the exeuction plans should be reused. The caveat being that select
> > access to the underlying tables needs to be granted to the users.
> >
> > Given that you have 30 columns and a large number of possible executions
> > (it appears you are going to allow any combination) , I would use
> > dynamic SQL.
> >
> > It's not like queries that say "Where (col = @col_val or @col_val is
> > null)" for 30 columns will be very efficient. I would be more concerned
> > about users entering criteria that won't be helped by an index or that
> > return too many rows. Consider using WITH (NOLOCK) on your queries to
> > prevent unnecessary locking. At least, that way, a big select statement
> > won't acquire shared locks on the tables.
> >
> >
> > --
> > David Gugick
> > Imceda Software
> > www.imceda.com
> >
>
> Thanks David. Actually, I was going to exec the Dynamic SQL from a stored
> procedure, so I'd still
> have to only grant exec to the proc to users.
>
> Many of the fields being queries are ints and the most common have indexes.
> The ones that are non-numeric
> are varchar(255) and the user can only search with a BEGINS with filter
> (i.e. @Filter+'%'). No
> contains with a wildcard before and after the string being searched for. I
> think that will help offset some
> performance concerns.
>
> Thanks for the NOLOCK hint. That's a good idea. I'll do that.
>
>
>

WJ wrote:
>
> Thanks David. Actually, I was going to exec the Dynamic SQL from a
> stored procedure, so I'd still
> have to only grant exec to the proc to users.
>
> Many of the fields being queries are ints and the most common have
> indexes. The ones that are non-numeric
> are varchar(255) and the user can only search with a BEGINS with
> filter (i.e. @Filter+'%'). No
> contains with a wildcard before and after the string being searched
> for. I think that will help offset some
> performance concerns.
>
> Thanks for the NOLOCK hint. That's a good idea. I'll do that.