Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

Need to Return Identity field from Stored Procedure

Hi I am very new to stored procedures.  I have the following table

      [OperatorID] [int] IDENTITY(3303,9) NOT NULL,
      [OperatorPassword] [varchar](50) NOT NULL,
      [FullName] [varchar](50) NOT NULL,
      [CompanyID] [int] NOT NULL,
      [Expires] [datetime] NOT NULL

I want to create a stored procedure to insert the data.  I am storing the OperatorID as an Int Identity type. When I Insert the record I need it to return the Identity value. How can I do this?

Also what would my T-SQL look like to read the output value?

USE db
if object_id('dbo.insertOperator') is not null
   drop procedure dbo.insertOperator
go
CREATE PROCEDURE insertOperator
	@OperatorPassword varchar(50),
	@CompanyID INT,
	@FullName VARCHAR(50),
	@Expires DATETIME
AS
INSERT INTO Operators
(
	CompanyID,
	FullName,
	OperatorPassword,
	Expires	
)
VALUES
(
	@CompanyID,
	@FullName,
	@OperatorPassword,
	@Expires
)

Open in new window

0
Charles Baldo
Asked:
Charles Baldo
1 Solution
 
Chris LuttrellSenior Database ArchitectCommented:
add the output parameter for OperatorId and get it from Scope_Identity after the insert as below.
To call it do something like this:

DECLARE @OperatorID INT

EXEC insertOperator
      @OperatorPassword = 'password', -- varchar(50)
      @CompanyID = 1, -- INT
      @FullName = 'Name', -- VARCHAR(50)
      @Expires = '2009-4-18 21:7:35.46', -- DATETIME
      @OperatorID = @OperatorID OUT -- INT

-- now @OperatorID has the new value to use where you need it
USE db
if object_id('dbo.insertOperator') is not null
   drop procedure dbo.insertOperator
go
CREATE PROCEDURE insertOperator
	@OperatorPassword varchar(50),
	@CompanyID INT,
	@FullName VARCHAR(50),
	@Expires DATETIME,
	@OperatorID INT OUT
AS
INSERT INTO Operators
(
	CompanyID,
	FullName,
	OperatorPassword,
	Expires	
)
VALUES
(
	@CompanyID,
	@FullName,
	@OperatorPassword,
	@Expires
)
SET @OperatorID = SCOPE_IDENTITY()
GO

Open in new window

0
 
Charles BaldoSoftware DeveloperAuthor Commented:
Thanks a ton, perfect
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now