CODEWITHSUNDEEP
sql-serverstored-procedures
Pirson
Pirson

Reputation: 75

How can I do if else conditional statement in where cluse

Please check my current stored procedure.

USE [CastingDatabase]
GO
/****** Object:  StoredProcedure [dbo].[Searching_Talents]    Script Date: 11/11/2015 1:41:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Searching_Talents]

@name nvarchar(100),
@age_from nvarchar(10),
@age_to nvarchar(10)


AS
BEGIN

    SELECT *
    FROM [dbo].[Talents]
    WHERE(
    ((@name is null) or (@name='') or ([dbo].[Talents].[Name] LIKE '%'+@name+'%'))
    AND
    (((@age_to is null) or (@age_to='') or (DATEDIFF(YEAR,Talents.DOB,GETDATE()) IN
    (

            SELECT CAST (Item as int) FROM dbo.SplitString(@age_to,',')
    )
    ))
    )   

    )

    ORDER BY Name
END

I want to add some if_else condition statements in second where clause.

How can I add?

I want to add like this

    AND(if(@age_from is null){---- my codes ----})

P.S. I am a newbie in stored procedure. Please help me.

Upvotes: 1

Views: 122

Answers (1)

Amel
Amel

Reputation: 708

Add CASE where you want to check some value. This doesn't apply only for procedures, it applies on all queries in sql server

...
AND (
    CASE 
        WHEN @age_from IS NULL
        THEN yourCode
    END
)

or if you have only to check if null and write something else, you can check it like this:

...
AND (ISNULL(@age_from, defaultValue)

and in this case, if value of age_from is null, sql server will put defaultValue which you state in above code.

Upvotes: 1

Related Questions