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

    Unanswered: stored procedure help

    I have the following stored procedure used for an insert:

    CREATE PROCEDURE dbo.ABR_REQ_HDR_INSERT
    @ddo varchar(50) = null,
    @ay char(4) = null,
    @requestor varchar(100) = null,
    @dt datetime = null,
    @subject varchar(250) = null,
    @description varchar(500) = null,
    @status varchar(10) = null

    AS
    Insert into dbo.ABR_REQ_HDR (ABR_DDO, ABR_AY, ABR_REQUESTOR, ABR_DT, ABR_SUBJECT, ABR_DESC, ABR_STATUS)
    values (UPPER(@ddo), @ay, UPPER(@requestor), @dt, UPPER(@subject), UPPER(@description), UPPER(@status))
    return @@identity
    GO

    Is is possible to concatenate several fields from this stored procedure and insert into a database field from the same stored procedure? So, as this procedure is executing it will concatenate @ay + @ddo + @@identity and insert into the database field I declare?

    If so, how?
    Thanks,
    -D-

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You'll need to capture @@IDENTITY first, before "return @@identity" by storing it into an INT variable:

    set @my_identity = scope_identity()
    set @my_variable = @ay + @ddo + @my_identity
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    ...
    As
    Declare @identity bigint, @error int

    Begin Tran
    Insert Into dbo.ABR_REQ_HDR (ABR_DDO, ABR_AY, ABR_REQUESTOR, ABR_DT
    , ABR_SUBJECT, ABR_DESC, ABR_STATUS)
    Values (@ddo, @ay, @requestor, @dt, @subject, @description, @status)

    Select @identity = Scope_Identity(), @error = @@ERROR

    If @error = 0
    Begin
    Insert WhackyTable(WhackyColumn)
    Values (@ay + @ddo + Cast(@identity As varchar))

    Select @error = @@ERROR
    End

    If @error = 0
    Begin
    Commit Tran
    Return(@identity)
    End
    Else
    Begin
    Rollback Tran
    Return(@error)
    End

    -- I got rid of your UPPER()'s so your users don't YELL at you. Case-sensitive db? Just say no to case-sensitivity; so 1980's.
    -- An improved version would check if you're in a transaction when the sp is called and behave accordingly.

Posting Permissions

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