Simple SQL Query (MSSQL2K)

McGintyMcGoo · 07-04-2004 10:41AM #1

I have a stored procedure that executes a number of SQL statements, but I was wondering if it is possible to do a comparison to "*" for all records.

To explain.
I know that this returns all records from a table
"SELECT * FROM tblCustomers"

I know that if I wished to retrieve a particular customer it would br
"SELECT * FROM tblCustomers WHERE cust_code = @Customer"
@Customer is the input parameter passed up from the application.

But instead of doing this ...
If (@Customer = 'All')
"SELECT * FROM tblCustomers"
Else
"SELECT * FROM tblCustomers WHERE cust_code = @Customer"

I would like to leave just one line
"SELECT * FROM tblCustomers WHERE cust_code = @Customer"
and if the customer wanted all records, it would read something like
"SELECT * FROM tblCustomers WHERE cust_code = *"

The above example is simplified and the real example is very complicated.

Cheers

Merrion · 07-04-2004 01:42PM

    SELECT * FROM tblCustomers 
    WHERE  cust_code = @Customer 
          OR   @Customer = 'ALL'

amen · 07-04-2004 03:11PM

SELECT * FROM tblCustomers
WHERE cust_code like CASE @Customer
WHEN 'ALL' THEN 'ALL
ELSE @Customer
end

this does what you want
not the most efficient query the use of the like is not really a good idea
why do you need to do this?
maybe rethink what you are trying to do ?

bonkey · 07-04-2004 03:41PM

I'm not sure that code will work, amen.

Its close though.

SELECT  *
  FROM  tblCustomers
  WHERE cust_code = CASE @Customer 
                      WHEN 'ALL' THEN cust_code
                      ELSE @Customer
                    END

McGinty - what this does :

when the parameter is 'ALL', then you end up with a WHERE clause which says :

where cust_code = cust_code

and when its not all, you end up with :

where cust_code = @cust_code

The only significant disadvantage is that on a large table, these should ideally be optimised differently. In the first case (ALL), the correct optimisation would be to ignore the where clause and do a table-scan. In the second case, the correct optimisation would be to use an index lookup on the cust_code field.

If the performance is a problem, you'd probbly be better going back to seperate queries rather than trying to tweak the optimisation....but thats just an opinion.

jc

amen · 08-04-2004 09:12AM

bonkey you are right
I meant to have cust_code

McGintyMcGoo · 08-04-2004 10:30AM

Thanks for the help guys!!

bonkey · 08-04-2004 10:45AM

No problem....although its reminded me that I have a half-written SQL manual that I should get around to finishing some of these days.

I love writing manuals. Oh joy.

jc

Simple SQL Query (MSSQL2K)

Comments