How to return PrimaryKey, Inserted datetime

• hoseam

  SSCertifiable

  Points: 5533

  More actions

  February 13, 2018 at 10:59 pm

  #344041

  Hi 

  I have a Procedure
  
CREATE PROCEDURE ReturnValues(
@ReturnValuesID INT OUT
@MemberID INT
@Name  VARCHAR(10)
@InsertedDate DATETIME OUT
)

  INSERT INTO dbTable(
MemberID
,Name
)
VALUES(
@MemberID
,@Name
)


  I want to return the primary key and the Inserted time.

  I thought of this
  
DECLARE @MyTableVar TABLE(
PK INT
,InsertedDt DATETIME
)

  INSERT INTO @MyTableVar
OUTPUT inserted.ReturnValuesID, inserted.InsertedDate


  But I'm getting this error:
  he OUTPUT clause cannot be used in an INSERT...EXEC statement.

  Is there any better solution or what am I doing wrong?

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  February 14, 2018 at 1:39 am

  #1979689

  If you're using the procedure in an INSERT INTO ... EXEC... Then you need to be very careful. You can't add more resultsets to the procedure, as they're break the insert. Given that, how do you want to return those two columns?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  February 14, 2018 at 10:23 am

  #1979775

  You could do in in a merge however you may want to consider this: ALTER TABLE dbTable ADD DateCreated datetime CONSTRAINT DFdbTableDateCreated DEFAULT GetDate()