CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
None
None

Reputation: 5670

If else inside SQL Where clause throws error

My Stored Procedure is like this

    alter  PROCEDURE ph_GetAllStaffAddressByCamp
    @CampCode  VARCHAR(20)

AS

IF NULLIF(@CampCode, '') IS NULL

    DECLARE @Year VARCHAR(2)
    EXEC cs_GetCurrentYear @Year OUTPUT

    SELECT DISTINCT [VolID], [CampID]  FROM [vStaffJobAndCamp]
 WHERE 
IF @CampCode IS NULL
    CampCode like @Year +%
ELSE
    CampCode = @CampCode

Everything looks okay to me, But it throws compile error like this

    Msg 156, Level 15, State 1, Procedure ph_GetAllStaffAddressByCamp, Line 14
Incorrect syntax near the keyword 'IF'.
Msg 102, Level 15, State 1, Procedure ph_GetAllStaffAddressByCamp, Line 15
Incorrect syntax near 'CampCode'.

Does any one know what I am doing wrong?

Upvotes: 1

Views: 409

Answers (4)

WadimX
WadimX

Reputation: 317

If you want a good execution plan, you should avoid OR logic in WHERE clauses.

ALTER PROCEDURE ph_GetAllStaffAddressByCamp
    @CampCode  VARCHAR(20)
AS
IF @CampCode IS NULL OR @CampCode = ''
BEGIN
    DECLARE @Year VARCHAR(2)
    EXEC cs_GetCurrentYear @Year OUTPUT
    SELECT DISTINCT [VolID], [CampID]  FROM [vStaffJobAndCamp]
    WHERE CampCode LIKE @Year+'%'
END
ELSE
    SELECT DISTINCT [VolID], [CampID]  FROM [vStaffJobAndCamp]
    WHERE CampCode = @CampCode

Upvotes: 0

sateesh kumar
sateesh kumar

Reputation: 19

case Campcode WHEN @CampCode IS NULL THEN CampCode like @Year +% ELSE CampCode = @CampCode END

use case instead of IF condition in where clause.

Upvotes: 0

MikkaRin
MikkaRin

Reputation: 3084

You forgot BEGIN...END in IF clause. And WHERE conditions was also wrong

    alter  PROCEDURE ph_GetAllStaffAddressByCamp
    @CampCode  VARCHAR(20)

AS

IF NULLIF(@CampCode, '') IS NULL
BEGIN
    DECLARE @Year VARCHAR(2)
    EXEC cs_GetCurrentYear @Year OUTPUT

    SELECT DISTINCT [VolID], [CampID]  FROM [vStaffJobAndCamp]
 WHERE 
(@CampCode IS NULL and CampCode like @Year +%) OR
(@CampCode IS NOT NULL and CampCode = @CampCode)
END

Upvotes: 1

Cristian Lupascu
Cristian Lupascu

Reputation: 40536

IF is not a valid SQL keyword. Therefore, you cannot use it inside a SELECT statement.

You could express those conditions in valid SQL like so:

WHERE 
  (@CampCode IS NULL AND CampCode like @Year + '%')
  OR
  (@CampCode is NOT NULL AND CampCode = @CampCode)

Upvotes: 1

Related Questions