Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    124

    Unanswered: sequence number help

    I have the following stored procedure:

    CREATE PROCEDURE dbo.ABR_HDR_INSERT

    @id int output,
    @status int,
    @mode int,
    @sessid varchar(100)

    AS

    declare @ay char(4)
    declare @ddo char(4)
    declare @abrid varchar(50)
    declare @seq_no int

    SELECT @ddo = a.DDO_DSCR_SHORT
    FROM dbo.DIM_DDO a
    JOIN dbo.Temp_ABR_HDR b
    ON a.DDO_ID = b.DDO
    WHERE b.SESSIONID = @sessid

    SELECT @ay = AY
    FROM dbo.Temp_ABR_HDR
    WHERE SESSIONID = @sessid


    -- set the default seq_no
    SELECT @seq_no = 1
    -- get the max abrid. if no record return the seq_no will be 1
    SELECT @seq_no = convert(integer, max(right(abrid, 4)))
    FROM dbo.ABR_HDR
    WHERE left(abrid, 7) = @ay + @ddo

    -- convert @seq_no to string prefix by 0
    SELECT @abrid = @ay + @ddo + right('0000' + rtrim(convert(char(4), @seq_no)), 4)


    Insert into dbo.ABR_HDR (ABRID, HDR_MODE, HDR_DDO, HDR_AY, HDR_REQUESTOR, HDR_DT, HDR_SUBJECT, HDR_DESCRIPTION, HDR_STATUS)

    SELECT

    @abrid,
    @mode,
    DDO,
    AY,
    REQUESTOR,
    DT,
    SUBJECT,
    DESCRIPTION,
    @status

    FROM dbo.Temp_ABR_HDR


    SELECT @id = @@identity

    return @id
    GO

    ABRID gets inserted as a <NULL> value. I can't figure out why? If I comment out the following then ABRID will insert without the sequence number:

    CREATE PROCEDURE dbo.ABR_HDR_INSERT

    @id int output,
    @status int,
    @mode int,
    @sessid varchar(100)

    AS

    declare @ay char(4)
    declare @ddo char(4)
    declare @abrid varchar(50)
    declare @seq_no int

    SELECT @ddo = a.DDO_DSCR_SHORT
    FROM dbo.DIM_DDO a
    JOIN dbo.Temp_ABR_HDR b
    ON a.DDO_ID = b.DDO
    WHERE b.SESSIONID = @sessid

    SELECT @ay = AY
    FROM dbo.Temp_ABR_HDR
    WHERE SESSIONID = @sessid


    -- set the default seq_no
    --SELECT @seq_no = 1
    -- get the max abrid. if no record return the seq_no will be 1
    --SELECT @seq_no = convert(integer, max(right(abrid, 4)))
    --FROM dbo.ABR_HDR
    --WHERE left(abrid, 7) = @ay + @ddo

    -- convert @seq_no to string prefix by 0
    --SELECT @abrid = @ay + @ddo + right('0000' + rtrim(convert(char(4),@seq_no)), 4)

    SELECT @abrid = @ay + UPPER(@ddo)

    Insert into dbo.ABR_HDR (ABRID, HDR_MODE, HDR_DDO, HDR_AY, HDR_REQUESTOR, HDR_DT, HDR_SUBJECT, HDR_DESCRIPTION, HDR_STATUS)

    SELECT

    @abrid,
    @mode,
    DDO,
    AY,
    REQUESTOR,
    DT,
    SUBJECT,
    DESCRIPTION,
    @status

    FROM dbo.Temp_ABR_HDR


    SELECT @id = @@identity

    return @id
    GO

    So, the code that sets the sequence number is what is causing the <NULL> value.

    Any help is appreciated.
    Thanks,
    -D-

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Looks like a problem of a + null = null What happens when you try this:
    Code:
         -- set the default seq_no
    SELECT @seq_no = 1
    -- get the max abrid. if no record return the seq_no will be 1
    SELECT @seq_no = isnull (convert(integer, max(right(abrid, 4))), 0)
    FROM dbo.ABR_HDR
    WHERE left(abrid, 7) = @ay + @ddo

  3. #3
    Join Date
    Jan 2004
    Posts
    124
    Oh so close...

    it didn't return a <null> value this time.

    It returned:

    2006FIN 0000

    It missed entering the @seq_no after the '0000', which should be 1

    Also, an added space was entered between "N" and the first '0'

    Thanks for your help. I appreciate it.
    Regards,
    -D-

  4. #4
    Join Date
    Jan 2004
    Posts
    124
    I think I've almost got it.

    Here is my revised stored procedure:

    CREATE PROCEDURE dbo.ABR_HDR_INSERT

    @id int output,
    @status int,
    @mode int,
    @sessid varchar(100)

    AS

    declare @ay char(4)
    declare @ddo char(4)
    declare @abrid varchar(50)
    declare @seq_no int

    SELECT @ddo = a.DDO_DSCR_SHORT
    FROM dbo.DIM_DDO a
    JOIN dbo.Temp_ABR_HDR b
    ON a.DDO_ID = b.DDO
    WHERE b.SESSIONID = @sessid

    SELECT @ay = AY
    FROM dbo.Temp_ABR_HDR
    WHERE SESSIONID = @sessid

    SELECT @seq_no = 1
    SELECT @seq_no = COALESCE(CONVERT(int, max(right(abrid,4))) +1, 1)
    From dbo.ABR_HDR
    WHERE (substring(abrid,1,4)) = @ay
    and (substring(abrid,5,3)) = upper(@ddo);

    -- convert @seq_no to string prefix by 0
    SELECT @abrid = @ay + @ddo + right('0000' + rtrim(convert(char(4), @seq_no)), 4)

    Insert into dbo.ABR_HDR (ABRID, HDR_MODE, HDR_DDO, HDR_AY, HDR_REQUESTOR, HDR_DT, HDR_SUBJECT, HDR_DESCRIPTION, HDR_STATUS)

    SELECT

    @abrid,
    @mode,
    DDO,
    AY,
    REQUESTOR,
    DT,
    SUBJECT,
    DESCRIPTION,
    @status

    FROM dbo.Temp_ABR_HDR


    SELECT @id = @@identity

    return @id
    GO

    This is very close to inserting the proper value except for the following problems.

    As an example, the following value is inserted when the stored procedure is executed:

    2006FIN 0001

    There is a space between 'N' and '0' and there are only three '000' instead of '0000'

    Otherwise, it appears to correctly inserting.

    What am I missing that is causing the space and not inserting the four zero's in the @seq_no parameter?

    Thanks,
    -D-

  5. #5
    Join Date
    Jan 2004
    Posts
    124
    Figured it out:

    CREATE PROCEDURE dbo.ABR_HDR_INSERT

    @id int output,
    @status int,
    @mode int,
    @sessid varchar(100)

    AS

    declare @ay char(4)
    declare @ddo char(3)
    declare @abrid varchar(50)
    declare @seq_no int

    SELECT @ddo = a.DDO_DSCR_SHORT
    FROM dbo.DIM_DDO a
    JOIN dbo.Temp_ABR_HDR b
    ON a.DDO_ID = b.DDO
    WHERE b.SESSIONID = @sessid

    SELECT @ay = AY
    FROM dbo.Temp_ABR_HDR
    WHERE SESSIONID = @sessid

    SELECT @seq_no = 1
    SELECT @seq_no = COALESCE(CONVERT(int, max(right(abrid,4))) +1, 1)
    From dbo.ABR_HDR
    WHERE (substring(abrid,1,4)) = @ay
    and (substring(abrid,5,3)) = upper(@ddo);

    -- convert @seq_no to string prefix by 0
    SELECT @abrid = @ay + @ddo + right('0000' + rtrim(convert(char(5), @seq_no)), 5)

    Insert into dbo.ABR_HDR (ABRID, HDR_MODE, HDR_DDO, HDR_AY, HDR_REQUESTOR, HDR_DT, HDR_SUBJECT, HDR_DESCRIPTION, HDR_STATUS)

    SELECT

    @abrid,
    @mode,
    DDO,
    AY,
    REQUESTOR,
    DT,
    SUBJECT,
    DESCRIPTION,
    @status

    FROM dbo.Temp_ABR_HDR


    SELECT @id = @@identity

    return @id
    GO

    Thanks,
    -D-

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •