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

    Unanswered: unique identifier

    I'm trying to create a unique identifier number that meets the following criteria. The unique identifier needs to be a concatenation of two values submitted from a form and the identity value (primary key) for the new record that is inserted into the database.

    So, if the first field is the year and the second field is a objnumber, the unique identifier number would have the format: ("YR" + "objnumber" + primary key value), where the year and object number are what the user selected in the form.

    I have a stored procedure that I use to handle the insert, which also returns @@identity for the purpose of passing that value into another stored procedure that inserts child records.

    So, within my stored procedure, is there a way I can create the unique identifier number and return that value back to the application? I'm not sure how to accomplish this?

    Here is my stored procedure:

    CREATE PROCEDURE dbo.REQ_HDR_INSERT
    @ddo varchar(50) = null,
    @requestor varchar(100) = null,
    @dt datetime = null,
    @abrtype varchar(20) = null,
    @subject varchar(250) = null,
    @description varchar(500) = null,
    @review char(10) = null,
    @ay char(4) = null,
    @origallo varchar(50) = null,
    @reqallo varchar(50) = null,
    @logl_del_dt datetime = null,
    @phys_del_dt datetime = null
    AS
    Insert into dbo.DIM_ABR_REQ_HDR (ABR_ddo, ABR_requestor, ABR_dt, ABR_type, ABR_subject, ABR_description, ABR_review, ABR_AY, ABR_orig_fund_allo, ABR_req_fund_allo, ABR_LOGL_DEL_DT, ABR_PHYS_DEL_DT)
    values (UPPER(@ddo), UPPER(@requestor), @dt, UPPER(@abrtype), UPPER(@subject), UPPER(@description), UPPER(@review), @ay, convert(money, @origallo), convert(money, @reqallo), @logl_del_dt, @phys_del_dt)
    return @@identity
    GO

    I would be using @ay and @ddo as the first two parts of the unique identifier number. Any help is appreciated.
    Thank you,
    -D-

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use something like:
    Code:
    CREATE PROCEDURE dbo.REQ_HDR_INSERT
       @ddo varchar(50) = null
    ,  @requestor varchar(100) = null
    ,  @dt datetime = null
    ,  @abrtype varchar(20) = null
    ,  @subject varchar(250) = null
    ,  @description varchar(500) = null
    ,  @review char(10) = null
    ,  @ay char(4) = null
    ,  @origallo varchar(50) = null
    ,  @reqallo varchar(50) = null
    ,  @logl_del_dt datetime = null
    ,  @phys_del_dt datetime = null
    ,  @junque = NULL OUTPUT
    AS
    
    DECLARE @i INT
    
    INSERT INTO dbo.DIM_ABR_REQ_HDR (
       ABR_ddo, ABR_requestor, ABR_dt
    ,  ABR_type, ABR_subject, ABR_description
    ,  ABR_review, ABR_AY, ABR_orig_fund_allo
    ,  ABR_req_fund_allo, ABR_LOGL_DEL_DT, ABR_PHYS_DEL_DT
      ) values (
       UPPER(@ddo), UPPER(@requestor), @dt
    ,  UPPER(@abrtype), UPPER(@subject), UPPER(@description)
    ,  UPPER(@review), @ay, convert(money, @origallo), convert(money, @reqallo)
    ,  @logl_del_dt, @phys_del_dt)
    
       SELECT @i = @@identity
       SELECT @junque = Convert(VARCHAR(50), @ay)
    +     Convert(VARCHAR(50), @ddo)
    +     Convert(VARCHAR(50), @i)
    
    RETURN @i
    GO
    -PatP

Posting Permissions

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