help on query

• marco alves

  Valued Member

  Points: 55

  More actions

  November 22, 2006 at 1:02 pm

  #90761

  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

  #673582

  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

• Satish Valekar

  SSC Veteran

  Points: 212

  More actions

  November 28, 2006 at 12:24 am

  #674242

  HI

  Select * from Customer Where (CUSTOMER = @customer Or @customer Is Null)

  AND (CITY = @id_city Or @id_city Is Null)

  AND (COUNTRY = @id_country or @id_country Is Null)

  Use the above query it may work properly

  send me reply if it is not

   

  satish