WHERE SQL Server Conditional Statement

Question

I would like to create an SP that will return all Country strings if CountryID not specified as a parameter. That's how I thought it might work, but he doesn't like it.

 ALTER PROCEDURE [dbo].[usp_return_countries] @CountryID AS INT = 0 AS BEGIN SELECT * FROM Countries WHERE Active = 1 IF @CountryID > 0 BEGIN AND @CountryID = CountryID END END 

thanks

PS I thought there might be a better way than just repeating the entire SELECT statement based on the specified condition.

Answer 1

Try it, it's elegant :)

  ALTER PROCEDURE [dbo].[usp_return_countries] @CountryID AS INT = 0 AS BEGIN SELECT * FROM Countries WHERE Active = 1 AND (@CountryID = 0 OR @CountryID = CountryID) END 

Answer 2

Do it like this:

 ALTER PROCEDURE [dbo].[usp_return_countries] @CountryID AS INT = 0 AS BEGIN IF @CountryID > 0 BEGIN SELECT * FROM Countries WHERE Active = 1 AND @CountryID = CountryID END ELSE BEGIN SELECT * FROM Countries WHERE Active = 1 END END 

Answer 3

Easy enough to wrap in one WHERE :

 SELECT * FROM Countries WHERE Active = 1 AND (@CountryID = 0 OR CountryID = @CountryID) 

Answer 4

Something like that?

 SELECT * FROM Countries WHERE Active = 1 AND (CountryID = @CountryID AND @CountryID <> 0) or (@CountryID = 0)