CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
user9079836
user9079836

Reputation:

How to write a TSQL query with nested CASE in IF statement

I need to generate a TSQL query like this:

IF @GenderOfEmployee IS NOT NULL
    CASE @GenderOfEmployee = 1
        THEN INSERT INTO @NotAllowedGenderOfJobPost (TagID) values (139)
        ELSE INSERT INTO @NotAllowedGenderOfJobPost (TagID) values (138)
    END;

I mean I want have a CASE statement in IF statement.
Is it possible?
Consider I don't want other similar ways to provide that query.
Thanks for your advanced help.

Upvotes: 1

Views: 121

Answers (2)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

Change like this

IF @GenderOfEmployee IS NOT NULL
BEGIN
     INSERT INTO @NotAllowedGenderOfJobPost (TagID) SELECT CASE WHEN @GenderOfEmployee = 1
        THEN 139
        ELSE 138
    END
END;

Upvotes: 1

HABO
HABO

Reputation: 15831

There is no need for a select to insert the result of a case expression:

declare @NotAllowedGenderOfJobPost as Table ( TagId Int );
declare @GenderOfEmployee as Int = NULL;

-- Try NULL.
if @GenderOfEmployee is not NULL
  insert into @NotAllowedGenderOfJobPost (TagId ) values
    ( case when @GenderOfEmployee = 1 then 139 else 138 end );
select * from @NotAllowedGenderOfJobPost;

-- Try 0.
set @GenderOfEmployee = 0;
if @GenderOfEmployee is not NULL
  insert into @NotAllowedGenderOfJobPost (TagId ) values
    ( case when @GenderOfEmployee = 1 then 139 else 138 end );
select * from @NotAllowedGenderOfJobPost;

-- Try 1.
set @GenderOfEmployee = 1;
if @GenderOfEmployee is not NULL
  insert into @NotAllowedGenderOfJobPost (TagId ) values
    ( case when @GenderOfEmployee = 1 then 139 else 138 end );
select * from @NotAllowedGenderOfJobPost;

Upvotes: 0

Related Questions