1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

SQL Server

Discussion in 'Databases' started by jimFFlagg, Nov 6, 2019.

  1. #1
    I am trying to make an Update Stored Procedure that will Null a value if a null is passed in the parameter. So I made the defaults a value we would never use "~" then did an IIF to determine if a null was passed but my logic is not working. Any advise on how to achieve what I am trying? Here is my code...

    /****** Object:  StoredProcedure [dbo].[USP_Account_Update2]    Script Date: 11/6/2019 1:17:05 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    -- =============================================
    -- Author:        Jim Flagg
    -- Create date: 10/30/19
    -- Description:    Updates the Accountnote table
    -- =============================================
    ALTER PROCEDURE [dbo].[USP_Account_Update2]
        -- Add the parameters for the stored procedure here
        --<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
        --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
        @AccountID                    bigint,             -- Read Only To seek on.
        @BindingKey                   varchar(10) = '~',
        @AcctNum                      varchar(10) = '~',
        @ClientId                     bigint = null,
        @AcctMark                     char(1) = '~',
        @AcctLink                     varchar(30) = '~',
        @TypeCodeId                   bigint = null,
        @PlacementId                  varchar(21) = '~',
        @FwdrSiteCd                   varchar(10) = '~',
        @FirmSiteCd                   varchar(10) = '~',
        @AuxNum                       varchar(30) = '~',
        @OtherAcctNum                 varchar(30) = '~',
        @OrigUserCodeId               bigint = null,
        @RespUserCodeId               bigint = null,
        @AssignedFromCodeId           bigint = null,
        @RcvAttyCodeId                bigint = null,
        @RcvAttyDate                  date = '1899-12-30',
        @OtherPartyCodeId             bigint = null,
        @ClientAgentCodeId            bigint = null,
        @OrigCredNote                 varchar(max) = '~',
        @TrxNote                      varchar(max) = '~',
        @Remarks                      varchar(max) = '~',
        @ClientCode_Translation       varchar(100) = '~',
        @TypeCode_Translation         varchar(100) = '~',
        @OrigCode_Translation         varchar(100) = '~',
        @RespCode_Translation         varchar(100) = '~',
        @AssignedFromCode_Translation varchar(100) = '~',
        @RcvAttyCode_Translation      varchar(100) = '~',
        @OtherPartyCode_Translation   varchar(100) = '~',
        @ClientAgentCode_Translation  varchar(100) = '~',
        @JST_PkARoot                  varchar(8) = '~'
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
    
        -- Insert statements for procedure here
        if @AccountId > 0
        begin
          Update Account
          Set
            BindingKey                   = iif(@BindingKey <> null, @BindingKey, BindingKey)                                                       ,
            AcctNum                      = iif(@AcctNum <> '~', @AcctNum, AcctNum)                                                                 ,
            ClientId                     = iif(@ClientId <> null, @ClientId, ClientId)                                                             ,
            AcctMark                     = iif(@AcctMark <> '~', @AcctMark, AcctMark)                                                              ,
            AcctLink                     = iif(@AcctLink <> '~', @AcctLink, AcctLink)                                                              ,
            TypeCodeId                   = iif(@TypeCodeId <> null, @TypeCodeId, TypeCodeId)                                                       ,
            PlacementId                  = iif(@PlacementId <> null, @PlacementId, PlacementId)                                                    ,
            FwdrSiteCd                   = iif(@FwdrSiteCd <> '~', @FwdrSiteCd, FwdrSiteCd)                                                        ,
            FirmSiteCd                   = iif(@FirmSiteCd <> '~', @FirmSiteCd, FirmSiteCd)                                                           ,
            AuxNum                       = iif(@AuxNum <> '~', @AuxNum, AuxNum)                                                                       ,
            OtherAcctNum                 = iif(@OtherAcctNum <> '~', @OtherAcctNum, OtherAcctNum)                                                   ,
            OrigUserCodeId               = iif(@OrigUserCodeId <> null, @OrigUserCodeId, OrigUserCodeId)                                           ,
            RespUserCodeId               = iif(@RespUserCodeId <> null, @RespUserCodeId, RespUserCodeId)                                           ,
            AssignedFromCodeId           = iif(@AssignedFromCodeId <> null, @AssignedFromCodeId, AssignedFromCodeId)                               ,
            RcvAttyCodeId                = iif(@RcvAttyCodeId <> null, @RcvAttyCodeId, RcvAttyCodeId)                                               ,
            RcvAttyDate                  = iif(@RcvAttyDate <> '1899-12-30', @RcvAttyDate, RcvAttyDate)                                               ,
            OtherPartyCodeId             = iif(@OtherPartyCodeId <> null, @OtherPartyCodeId, OtherPartyCodeId)                                       ,
            ClientAgentCodeId            = iif(@ClientAgentCodeId <> null, @ClientAgentCodeId, ClientAgentCodeId)                                   ,
            OrigCredNote                 = iif(@OrigCredNote <> '~', @ClientAgentCodeId, ClientAgentCodeId)                                           ,
            TrxNote                      = iif(@TrxNote <> '~', @TrxNote, TrxNote)                                                                   ,
            Remarks                      = iif(@Remarks <> '~', @Remarks, Remarks)                                                                   ,
            ClientCode_Translation       = iif(@ClientCode_Translation <> '~', @ClientCode_Translation, ClientCode_Translation)                    ,
            TypeCode_Translation         = iif(@TypeCode_Translation <> '~', @TypeCode_Translation, TypeCode_Translation)                           ,
            OrigCode_Translation         = iif(@OrigCode_Translation <> '~', @OrigCode_Translation, OrigCode_Translation)                           ,
            RespCode_Translation         = iif(@RespCode_Translation <> '~', @RespCode_Translation, RespCode_Translation)                           ,
            AssignedFromCode_Translation = iif(@AssignedFromCode_Translation <> '~', @AssignedFromCode_Translation, AssignedFromCode_Translation)  ,
            RcvAttyCode_Translation      = iif(@RcvAttyCode_Translation <> '~', @RcvAttyCode_Translation, RcvAttyCode_Translation)                   ,
            OtherPartyCode_Translation   = iif(@OtherPartyCode_Translation <> '~', @OtherPartyCode_Translation, OtherPartyCode_Translation)           ,
            ClientAgentCode_Translation  = iif(@ClientAgentCode_Translation <> '~', @ClientAgentCode_Translation, ClientAgentCode_Translation)       ,
            JST_PkARoot                  = iif(@JST_PkARoot <> '~', @JST_PkARoot, JST_PkARoot)
          Where AccountID = @AccountID
          -- Code here for Updating Dependant Orphans or we could put the Orphan updates in a On Update Trigger.
        end
    END
    Code (SQL):

    SEMrush
     
    jimFFlagg, Nov 6, 2019 IP
    SEMrush
  2. jimFFlagg

    jimFFlagg Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #2
    Never Mind. I figured out how to get it to work. I just used a " Is Null or <> '~' ".

    Thanks,

    Jim
     
    jimFFlagg, Nov 8, 2019 IP