help on query

• marco alves

  Valued Member

  Points: 55

  More actions

  November 22, 2006 at 1:01 pm

  #175466

  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.

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  November 22, 2006 at 1:09 pm

  #673583

  Please do not cross-post.  We monitor all boards.

  Finish the thread here pls : http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=325058

• hr_sn

  Ten Centuries

  Points: 1092

  More actions

  November 22, 2006 at 5:20 pm

  #673621

  SELECT *

  FROM CUSTOMER

  WHERE customer = ISNULL(@customer,customer)

  AND city = ISNULL(@city, city)

  AND country = ISNULL(@Country, country)

  or

  SELECT *

  FROM CUSTOMER

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

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

  AND (@country IS NULL OR country = @Country)

  BTW, if you have searched the forum, you would've found heaps of examples.

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  November 22, 2006 at 8:31 pm

  #673630

  We already answered that question in the link I posted.  Also the question was to use ORs and not ANDs in the query .