help on query

Question

Post reply

help on query

marco alves

Valued Member

Points: 55
More actions
November 22, 2006 at 1:03 pm

#97900

Hi all.
First, sorry for my bad english. I need some help on the following:
I have a stored procedure that query my Customer table and it has three parameters:
@customer varchar(500), @id_city int and @id_country int
all of them accepts null as incoming
Now, I want to get the registers from my Customer like this:
If I pass null to three parameters, all of my registers will be returned.
If I pass null to @id_city and some value to @customer and @id_country all registers that has @customer OR (not AND) @id_country will be returned.
If I pass null to @customer and some value to @id_city and @id_country all registers that has @id_city OR (not AND) @id_country will be returned.
And so on.
Example:
CUSTOMER TABLE:
#REGISTER
CUSTOMER
CITY
COUNTRY
1
PELE
SANTOS
BRAZIL
2
RONALDINHO
BARCELONA
SPAIN
3
ROMARIO
RIO DE JANEIRO
BRAZIL
4
ADRIANO
MILAN
ITALY
If I pass @customer = 'PELE' and Country = 'Spain' I want to be returned registers 1 and 2.
If I pass null to all three parameters, I want to be returned all four registers.
If I pass @city = 'santos' and @customer = 'ronaldinho' and @country = 'italy' registers 1,2 and 4 will be returned.
PS: I don't want dynamic SQL.
Please, help me.
Best regards,
Marco Alves.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 1

DECLARE @Customers TABLE(

REGISTER int,

CUSTOMER varchar(50),

CITY varchar(50),

COUNTRY varchar(50))

INSERT INTO @Customers

SELECT 1 ,'PELE', 'SANTOS' ,'BRAZIL' UNION ALL

SELECT 2 ,'RONALDINHO', 'BARCELONA' ,'SPAIN' UNION ALL

SELECT 3 ,'ROMARIO', 'RIO DE JANEIRO' ,'BRAZIL' UNION ALL

SELECT 4 ,'ADRIANO', 'MILAN' ,'ITALY'

SELECT * FROM @Customers

DECLARE @customer varchar(50)

DECLARE @id_city varchar(50)

DECLARE @id_country varchar(50)

SET @customer=NULL

SET @id_city=NULL

SET @id_country=NULL

SELECT REGISTER FROM @Customers

WHERE (

(@customer IS NULL AND @id_city IS NULL AND @id_country IS NULL)

OR Customer=@Customer OR CITY=@id_city OR COUNTRY=@id_country)

SET @customer='PELE'

SET @id_city=NULL

SET @id_country='Spain'

SELECT REGISTER FROM @Customers

WHERE (

(@customer IS NULL AND @id_city IS NULL AND @id_country IS NULL)

OR Customer=@Customer OR CITY=@id_city OR COUNTRY=@id_country)

SET @customer='ronaldinho'

SET @id_city='santos'

SET @id_country='italy'

SELECT REGISTER FROM @Customers

WHERE (

(@customer IS NULL AND @id_city IS NULL AND @id_country IS NULL)

OR Customer=@Customer OR CITY=@id_city OR COUNTRY=@id_country)

Kindest Regards,

Vasc

David Burrows SSC Guru Points: 65143 More actions · Answer 2

I would do it like this

SELECT REGISTER

FROM @Customers

WHERE (@customer IS NULL OR CUSTOMER = @customer)

AND (@id_city IS NULL OR @id_city = CITY)

AND(@id_country IS NULL OR @id_country = COUNTRY)

Also you could do it like this

SELECT REGISTER

FROM @Customers

WHERE CUSTOMER = ISNULL(@customer,CUSTOMER)

AND CITY = ISNULL(@id_city,CITY)

AND COUNTRY = ISNULL(@id_country,COUNTRY)

You will need to check performance but since you are checking 3 columns a table or index scan would be involved anyway

Far away is close at hand in the images of elsewhere.
Anon.

marco alves Valued Member Points: 55 More actions · Answer 3

marco alves

Valued Member

Points: 55

November 23, 2006 at 8:58 am

#673703

THANKS A LOT!!!

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 4

those queries are not returning the weired desired result

Kindest Regards,

Vasc

David Burrows SSC Guru Points: 65143 More actions · Answer 5

those queries are not returning the weired desired result

Your right

Forgot wanted OR not AND

SELECT REGISTER

FROM @Customers

WHERE (@customer IS NULL OR CUSTOMER = @customer)

OR (@city IS NULL OR @city = CITY)

OR(@country IS NULL OR @country = COUNTRY)

SELECT REGISTER

FROM @Customers

WHERE CUSTOMER = ISNULL(@customer,CUSTOMER)

OR CITY = ISNULL(@city,CITY)

OR COUNTRY = ISNULL(@country,COUNTRY)

Far away is close at hand in the images of elsewhere.
Anon.

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 6

still... not the desired result

SET @customer='PELE'

SET @id_city=NULL

SET @id_country='Spain'

Kindest Regards,

Vasc

David Burrows SSC Guru Points: 65143 More actions · Answer 7

damn

only seems to work for AND's

Far away is close at hand in the images of elsewhere.
Anon.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Just for kicks... I had to same solution than vasc posted (about 30 secs before me), and I was getting the right results. So I see no point in you guys trying to refind the right solution .

marco alves Valued Member Points: 55 More actions · Answer 9

Hey.

For test purposes, I'd made the same logic but using dynamic sql. And the query is faster than the queries posted here. Can someone explain this?

Best regards,

Marco Alves.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Using the ors, the server has to assume that all rows may have to be returned. The fastest way to do this is using a index scan. When using dynamic sql, an index seek may be available and used... hence making the query faster.