Dynamic Query

I need to write a stored proceed that has 15 parameters that returns a
recordset. Any one of these parameters may contain values.
EX: @.Lname = ''
@.Phone = '1234567890'
@.Fname = 'JANE'
@.City = 'LA'
@.State = ''
The main part of the proc is a dynamically created SELECT statement
where the parameters are used in the WHERE clause. EX: @.SQL = 'SELECT
* FROM Table WHERE '. Only parameters with values must be included in
the WHERE clause. And any parameter after the first one should have
'AND'. So the query should look like this:
@.SQL = 'SELECT * FROM Table WHERE '
@.SQL = @.SQL + ' phone = ' + @.phone
@.SQL = @.SQL + ' AND Fname = ' + @.Fname
How can I figure out which is the first parameter that contains a
value so not to include an AND condition and then add the AND for the
rest of the parameters?
Thanks,
NinelYou cud write
set @.SQL = 'SELECT * FROM Table WHERE 1=1'
If isnull(@.phone ,'') <> ''
select @.sql = @.sql + '
AND Phone = @.phone'
If isnull(@.Fname ,'')<> ''
select @.sql = @.sql + '
AND FName = @.Fname'
exec (@.sql)
Untested, shud work
Prad
"ninel" <ngorbunov@.onetouchdirect-dot-com.no-spam.invalid> wrote in message
news:t4CdnXjfGpsWtvLfRVn_vA@.giganews.com...
>I need to write a stored proceed that has 15 parameters that returns a
> recordset. Any one of these parameters may contain values.
> EX: @.Lname = ''
> @.Phone = '1234567890'
> @.Fname = 'JANE'
> @.City = 'LA'
> @.State = ''
> The main part of the proc is a dynamically created SELECT statement
> where the parameters are used in the WHERE clause. EX: @.SQL = 'SELECT
> * FROM Table WHERE '. Only parameters with values must be included in
> the WHERE clause. And any parameter after the first one should have
> 'AND'. So the query should look like this:
> @.SQL = 'SELECT * FROM Table WHERE '
> @.SQL = @.SQL + ' phone = ' + @.phone
> @.SQL = @.SQL + ' AND Fname = ' + @.Fname
> How can I figure out which is the first parameter that contains a
> value so not to include an AND condition and then add the AND for the
> rest of the parameters?
> Thanks,
> Ninel
>|||Hi
This will not work as @.phone or @.Fname will not be in scope. Check out
http://www.sommarskog.se/dyn-search.html for working examples.
John
"Pradeep Kutty" wrote:

> You cud write
> set @.SQL = 'SELECT * FROM Table WHERE 1=1'
> If isnull(@.phone ,'') <> ''
> select @.sql = @.sql + '
> AND Phone = @.phone'
> If isnull(@.Fname ,'')<> ''
> select @.sql = @.sql + '
> AND FName = @.Fname'
> exec (@.sql)
> Untested, shud work
> Prad
> "ninel" <ngorbunov@.onetouchdirect-dot-com.no-spam.invalid> wrote in messag
e
> news:t4CdnXjfGpsWtvLfRVn_vA@.giganews.com...
>
>|||Avoid dynamic SQL and procedures with more than five parameters.
SELECT *
FROM Foobar
WHERE first_name = COALESCE(@.my_first_name, first_name)
AND last_name = COALESCE(@.my_last_name, last_name)
AND ... ;|||On 27 Apr 2005 07:46:33 -0700, --CELKO-- wrote:

> Avoid dynamic SQL and procedures with more than five parameters.
> SELECT *
> FROM Foobar
> WHERE first_name = COALESCE(@.my_first_name, first_name)
> AND last_name = COALESCE(@.my_last_name, last_name)
> AND ... ;
Is "five parameters" an arbitrary limit based on experience?
I can vouch for the fact that when there are too many parameters, the
optimizer has a really hard time figuring out a good plan. It will do crazy
things like a table scan to compare NULLs with every row, when it could
just get the desired answer from a primary key.
In one instance I "unrolled" the query into a set of the three most often
used queries, choosing the correct one to use based on IF statements.
(Programmer insisted on a single stored procedure for looking up customer
records, when the operator would sometimes only know the last name and
state, sometimes would have member ID, sometimes would have last name,
state and some other data ...)|||>> Is "five parameters" an arbitrary limit based on experience? <<
In 1956 by a psychologist named Miller published a short article
entitled "The Magical Number Seven Plus or Minus Two: Some Limits on
Our Capacity for Processing Information" that collected a lot of data
together in one place and this has been confirmed over and over again.
It is a classic paper and it ought to be out there.
The idea is that you can juggle five things fairly well, seven is when
it gets to hard and nine requires that you train for it and it is just
about impossible to get to ten things without being a savant. What you
have to do is "chunking" things to reduce the number of distinct
elements -- so (longtitude, latitude) becomes "location" rather than
two data elements.
the optimizer has a really hard time figuring out a good plan. <<
That is another "Law of Five". There are 3 ways to squence two tables
for processing, 6 ways to squence three tables, 24 ways to squence
four tables, and 120 ways to arrange five tables. Big jump at five!
And the optimizer starts to choke.
queries, choosing the correct one to use based on IF statements. <<
While I like to avoid IF-THEN control flow, it sounds like a good way
to do it in this case.sql