Post reply

Using CASE or IF with INSERT

  • 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

  • July 5, 2005 at 3:38 pm

    #571040

    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.

     

  • July 5, 2005 at 3:42 pm

    #571041

    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)

  • July 5, 2005 at 3:54 pm

    #571045

    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

  • July 5, 2005 at 8:40 pm

    #571073

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

  • July 6, 2005 at 8:33 am

    #571261

    Thanks for the help!

    CSDunn

  • July 6, 2005 at 8:35 am

    #571262

    I knew it!  


    * Noel

  • July 6, 2005 at 8:45 am

    #571270

    A promess is a promess .

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

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