Stored Procedure return default value?

Question

in my database, there is a Stored Procedure with INSERT INTO statement. The problem is I want this stored procedure will return the StudentCode default value, I think it's impossible to use the SELECT TOP 1 statement to get this value because there may be multiple rows inserted at the same time. Any help or suggestions?. Thanks very much

ALTER PROC [dbo].[AddStudent] 
@StudentName NVARCHAR(255),
@DoB DATETIME,
@Parent NVARCHAR(255),
@ParentContact VARCHAR(16),
@Address NVARCHAR(255),
@Class VARCHAR(6),
AS
INSERT INTO dbo.Student
        ( StudentCode , --I set default this column
          StudentName,
          DateOfBirth
          NameParrent ,
          PhoneContact ,
          AddressParent ,
          Class ,
        )
VALUES  ( DEFAULT , --StudentCode varchar(5)
          @StudentName, --StudentName nvarchar(255)
          @DoB, --DateOfBirth datetime
          @Parent, --NameParrent nvarchar(255)
          @ParentContact, --PhoneContact varchar(16)
          @Address, --AddressParent nvarchar(255)
          @Class --Class varchar(6)
        )
-- How to return StudentCode field

So the same student could register hundreds of times and your code would happily accept that and add hundreds of rows for the same person? And no - date of birth is **DATE** not DATETIME. — SMor, Mar 21 '20 at 12:42

score 1 · Answer 1 · answered Mar 21 '20 at 02:35

You need to Return value after INSERT

DECLARE @generated_StudentCode  table(StudentCode varchar(5))
INSERT INTO dbo.Student
        ( StudentCode, --I set default this column
          StudentName,
          DateOfBirth
          NameParrent ,
          PhoneContact ,
          AddressParent ,
          Class ,
        )

OUTPUT inserted.StudentCode INTO @generated_keys

VALUES  ( DEFAULT , --StudentCode varchar(5)
          @StudentName, --StudentName nvarchar(255)
          @DoB, --DateOfBirth datetime
          @Parent, --NameParrent nvarchar(255)
          @ParentContact, --PhoneContact varchar(16)
          @Address, --AddressParent nvarchar(255)
          @Class --Class varchar(6)
        )

SELECT TOP 1 * FROM @generated_StudentCode

Read the following thread to have a better understanding

SQL Server - Return value after INSERT

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

Use OUTPUT INSERTED clause, as explained into official docs:-

INSERTED

Is a column prefix that specifies the value added by the insert or update operation. Columns prefixed with INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed but before triggers are executed.

so your code is going to be like this:- (is not tested, but it guides you to accurate code)

ALTER PROC [dbo].[AddStudent] 
@StudentName NVARCHAR(255),
@DoB DATETIME,
@Parent NVARCHAR(255),
@ParentContact VARCHAR(16),
@Address NVARCHAR(255),
@Class VARCHAR(6),

AS

DECLARE @StudentCodeInserted  varchar(5)

INSERT INTO dbo.Student
        ( StudentCode, --I set default this column
          StudentName,
          DateOfBirth
          NameParrent ,
          PhoneContact ,
          AddressParent ,
          Class 
        )

OUTPUT inserted.StudentCode INTO @StudentCodeInserted

VALUES  ( DEFAULT , --StudentCode varchar(5)
          @StudentName, --StudentName nvarchar(255)
          @DoB, --DateOfBirth datetime
          @Parent, --NameParrent nvarchar(255)
          @ParentContact, --PhoneContact varchar(16)
          @Address, --AddressParent nvarchar(255)
          @Class --Class varchar(6)
        )
        
Select @StudentCodeInserted as StudentCodeInserted

Stored Procedure return default value?

2 Answers2