Conditional WHERE clause in SP

SELECT a.1
FROM TableA a
WHERE a.Field1 = 5
AND a.Field2 = 5
AND a.Field3 = 5

I would like to change the above query to this...

SELECT a.1
FROM TableA a
WHERE a.Field1 = 5
AND a.Field2 = 5
{if @ID <> 0 then add this to the WHERE clause } AND a.Field3 =@ID {if @ID = 0, leave it out entirely, and just use WHERE and the first AND}

Thanks in advance.
-Jim
LVL 66
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?
 
flavoCommented:
CREATE PROCEDURE pJimbo
(
@ID AS INT
)

AS

IF @ID = 0
  SELECT a.1
  FROM TableA a
  WHERE a.Field1 = 5
  AND a.Field2 = 5
ELSE
  SELECT a.1
  FROM TableA a
  WHERE a.Field1 = 5
  AND a.Field2 = 5
  AND a.Id = @ID
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
SELECT a.1
FROM TableA a
WHERE a.Field1 = 5
AND a.Field2 = 5
AND ( @ID = 0 OR  a.ID = @ID )
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
CREATE PROCEDURE Test(
@ID AS INT)

AS

IF @ID = 0
  SELECT a.Field1
  FROM TableA a
  WHERE a.Field1 = 5
  AND a.Field2 = CASE(@id) WHEN 0 then 5 ELSE @ID end
GO
0
 
imran_fastCommented:
CREATE PROCEDURE ProcedureName(
@ID  INT = 0)

AS

 SELECT a.[1]
FROM TableA a
WHERE a.Field1 = 5
AND a.Field2 = 5
and (a.FIELD3 = @ID OR @ID = 0)

GO
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Thanks guys.  Sorry for the late response.  -Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.