Using CASE or IF with INSERT

Question

Post reply

Using CASE or IF with INSERT

cdun2

SSCertifiable

Points: 7893
More actions
July 5, 2005 at 3:25 pm

#76366

Hello,
Within an INSERT statement where the VALUES are expressed, is it possible to evaluate one or more of the values with CASE or IF statements?
Consider the following code;
INSERT INTO snbat011_app_clnt
     (entr_cd,
      app_id,
      clnt_id,
      clnt_type,
      clnt_frst_name,
      clnt_mid_name,
      clnt_lst_name,
      clnt_gender)
VALUES
(@EntrCode, @AppId, @ClientId, @FirstName, @MidName, @LastName,
If @FormType = 'SimplifiedApp Or (@FormType = 'AccidentApp' And @FormId = 'AccidentAppRls0304') Then
        @Gender
Else
         '' )
The entry of a value for clnt_gender depends on the statements in the If condition. If one condition is met, then the parameter value in @Gender is used, otherwise, the entry is an empty string.
How would you recommend that I handle this kind of situation?
Thank you for your help!
CSDunn

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

eagoss Old Hand Points: 307 More actions · Answer 1

I don't believe you can embed a CASE statement within an INSERT. I've never gotten it to work.

However, one possibility would be to create a UDF that takes the parameter and returns what you want inserted.

Ray M SSC-Insane Points: 21093 More actions · Answer 2

How about Evaluate the @FormType and @FormId parameters, to set the value of Gender ID then insert.

You cannot do it inline in the Values Statement.

How about.

If @FormType = 'SimplifiedApp' Or

(@FormType = 'AccidentApp' And @FormId = 'AccidentAppRls0304')

Set @Gender = @Gender

else

set @Gender = ''

INSERT INTO snbat011_app_clnt

(entr_cd,

app_id,

clnt_id,

clnt_type,

clnt_frst_name,

clnt_mid_name,

clnt_lst_name,

clnt_gender)

VALUES

(@EntrCode, @AppId, @ClientId, @FirstName, @MidName, @LastName, @Gender)

noeld SSC Guru Points: 96590 More actions · Answer 3

How about:

INSERT INTO snbat011_app_clnt

(entr_cd,

app_id,

clnt_id,

clnt_type,

clnt_frst_name,

clnt_mid_name,

clnt_lst_name,

clnt_gender)

SELECT

@EntrCode, @AppId, @ClientId, @FirstName, @MidName, @LastName

, CASE WHEN @FormType = 'SimplifiedApp' Or (@FormType = 'AccidentApp' And @FormId = 'AccidentAppRls0304') Then

@Gender ELSE '' END as Gender

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

As promised I have to agree with Noeld's solution .

cdun2 SSCertifiable Points: 7893 More actions · Answer 5

cdun2

SSCertifiable

Points: 7893

July 6, 2005 at 8:33 am

#571261

Thanks for the help!

CSDunn

noeld SSC Guru Points: 96590 More actions · Answer 6

noeld

SSC Guru

Points: 96590

July 6, 2005 at 8:35 am

#571262

I knew it!

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Ninja's_RGR'us

SSC Guru

Points: 294069

July 6, 2005 at 8:45 am

#571270

A promess is a promess .