0

I have a table definition like this:

ID  int /* identity column */

SecondID nvarchar(50) /* ComputedColumn */

birthdate datet */ Simple Column */

I would like to insert values on this table and I would like to get the inserted value as output, the first ID, I got it using the scope_identity function.

Computed Function : ID * 1000

as an example, I would like to insert these values : (1,1000,12/08/2021)

What can I get from now is only the ID,

CREATE PROCEDURE [dbo].[USER_insert]
@birthdate date,
@SecondIDOut nvarchar(50) output

Begin
AS
INSERT INTO [dbo].[User]
           ([birthdate])
     VALUES
           (@birthdate)
           
    SET @ID = scope_identity()
    /* SET @SecondIDOut = ?? what can I set here */

END

How can I get the SecondID after executing the SQL insert statement?

abdou31
  • 45
  • 1
  • 8
  • Use an `OUTPUT` clause. – Thom A Aug 12 '21 at 11:06
  • Already used , I will update my question – abdou31 Aug 12 '21 at 11:08
  • `birthdate` is a DATE, not a DATETIME. THINK before you write code. If you really want the value calculated for SecondID, you must retrieve and pass it back to the caller of the procedure in some fashion. You could add another output parameter or you could simply return the inserted row as a resultset. You choose. – SMor Aug 12 '21 at 11:13

1 Answers1

0

Use an OUTPUT clause:

DECLARE @vals TABLE (id int, secondid varchar(30));

INSERT INTO [dbo].[User] ([birthdate])
    OUTPUT inserted.id, inserted.secondid INTO @vals;
     VALUES
           (@birthdate)
           
SELECT *
FROM @vals;

The last statement is just for example. You can assign the values to parameters if you prefer.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • You didn't show how can I assign the `inserted.id` to the parameter that I've added?? I mean in the Set statement? what should I write to get the output value ?? – abdou31 Aug 12 '21 at 11:17
  • Sorry , but this is not a solution because I insert every time new row , how can I get the last row from the table @vals? – abdou31 Aug 12 '21 at 11:22
  • @abdou31 . . . Read about the `OUTPUT` clause. It does what you want. – Gordon Linoff Aug 12 '21 at 12:20