Solved

Stored procedure. Insert then return primary key.

Posted on 2010-08-27
15
677 Views
Last Modified: 2012-05-10
I need a quick example of writing a stored procedure that inserts a record. then returns the primary key. Please no links.

The primary key is an integer that autofills in increments.
0
Comment
Question by:techpr0
  • 4
  • 3
  • 2
  • +3
15 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33544536
Check this code

Raj
CREATE TABLE YourTable

(

	ID INT IDENTITY(1,1),

	NAME VARCHAR(50)

)





CREATE PROCEDURE usp_InsertRecord

(

	@name	VARCHAR(50)

	@newID	INT OUTPUT

)

AS

BEGIN

	INSERT INTO YourTable (name) VALUES (@name)



	SET @newID = SCOPE_IDENTITY()

END

GO

Open in new window

0
 
LVL 4

Accepted Solution

by:
zmorvik earned 75 total points
ID: 33544543
Write your stored procedure and then at the end put

SELECT @@IDENTITY


For example:

CREATE PROCEDURE TestProcedure
      -- Add the parameters for the stored procedure here
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      INSERT INTO TestTable (column1, column2) VALUES ('test1', 'test2')

SELECT @@IDENTITY
END


If you are returning this to an application you are writing, you would change the SELECT @@IDENTIY to
SET @VariableName = SELECT @@IDENTITY
0
 

Author Comment

by:techpr0
ID: 33544638
I couldn't get it to work. Yes it is for an application.

I'm using this to get the returned value in the application.

Dim strCert As String = command.Parameters("RetVal").Value
ALTER PROCEDURE [dbo].[sp_AddRecord] 

	(

		@SerialNumber varchar(50)= NULL,

		@Company varchar(50)= NULL,

		@Invoice varchar(50)= NULL,

		@Inspection varchar(50)= NULL,

		@PO varchar(50) = NULL,

		@date datetime,

		@EditedBy varchar(50),

		@DateAdded datetime

	)

AS

BEGIN

	SET NOCOUNT ON;

	DECLARE @RetVal As int

	DECLARE @RetID As int

	SET @RetVal = 0

	

	If Exists(Select 1 from [lxp_1z3etnasf_site_aplus_net].[dbo].[Company] WHERE [Name] = @Company)

	BEGIN

		--Get Id if it does exist

		SELECT @RetID = [ID] FROM [lxp_1z3etnasf_site_aplus_net].[dbo].[Company] WHERE [Name] = @Company

		--Insert Login and link to Company table

		INSERT INTO [lxp_1z3etnasf_site_aplus_net].[dbo].[Certifications]

           ([Serial Number]

           ,[Invoice Authorization]

           ,[Inspection Number]

           ,[PO Number]

           ,[Active]

           ,[Date]

           ,[EditedBy]

           ,[DateAdded]

           ,[CompanyID])

		 VALUES

           (@SerialNumber

           ,@Invoice

           ,@Inspection

           ,@PO

           ,'True'

           ,@Date

           ,@EditedBy

           ,@DateAdded

           ,@RetID)

           

           Set @RetVal = SCOPE_IDENTITY()

	END

	ELSE

	BEGIN

		--Insert New Company

		INSERT INTO [lxp_1z3etnasf_site_aplus_net].[dbo].[Company]

           ([Name])

		VALUES

           (@Company)

		--Get just created Company id

		SELECT @RetID = [ID] FROM [lxp_1z3etnasf_site_aplus_net].[dbo].[Company] WHERE [Name] = @Company

		--Insert Login and link to Company table

		INSERT INTO [lxp_1z3etnasf_site_aplus_net].[dbo].[Certifications]

           ([Serial Number]

           ,[Invoice Authorization]

           ,[Inspection Number]

           ,[PO Number]

           ,[Active]

           ,[Date]

           ,[EditedBy]

           ,[DateAdded]

           ,[CompanyID])

		 VALUES

           (@SerialNumber

           ,@Invoice

           ,@Inspection

           ,@PO

           ,'True'

           ,@Date

           ,@EditedBy

           ,@DateAdded

           ,@RetID)

           

           Set @RetVal = SCOPE_IDENTITY()

	END

	END

Open in new window

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33544662
Add one output parameter to your stored procedure
@RetVal INT OUTPUT
And remove its declaration inside Stored procedure

Raj
0
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 50 total points
ID: 33544712
I think coding part will look like attached

Raj
Dim parameter As SqlParameter = command.Parameters.Add("@Retval", SqlDbType.Int)

parameter.Direction = ParameterDirection.Output

command.CommandText = "sp_AddRecord"

command.ExecuteNonQuery()

Dim outResult As String = parameter.Value.ToString()

Open in new window

0
 

Author Comment

by:techpr0
ID: 33547232
Changed to the following based on your previous response, but i get an error.

Incorrect Syntax near '@RetVal'
USE [lxp_1z3etnasf_site_aplus_net]

GO

/****** Object:  StoredProcedure [dbo].[sp_AddRecord]    Script Date: 08/27/2010 17:45:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO







-- =============================================

-- Author:		Roy Miller

-- Create date: 12/1/2009

-- Description:	Add New Record

-- =============================================

ALTER PROCEDURE [dbo].[sp_AddRecord] 

	(

		@SerialNumber varchar(50)= NULL,

		@Company varchar(50)= NULL,

		@Invoice varchar(50)= NULL,

		@Inspection varchar(50)= NULL,

		@PO varchar(50) = NULL,

		@date datetime,

		@EditedBy varchar(50),

		@DateAdded datetime

	)

AS

BEGIN

	SET NOCOUNT ON;

	DECLARE @RetID As int

	SET @RetVal = 0

	

	If Exists(Select 1 from [lxp_1z3etnasf_site_aplus_net].[dbo].[Company] WHERE [Name] = @Company)

	BEGIN

		--Get Id if it does exist

		SELECT @RetID = [ID] FROM [lxp_1z3etnasf_site_aplus_net].[dbo].[Company] WHERE [Name] = @Company

		--Insert Login and link to Company table

		INSERT INTO [lxp_1z3etnasf_site_aplus_net].[dbo].[Certifications]

           ([Serial Number]

           ,[Invoice Authorization]

           ,[Inspection Number]

           ,[PO Number]

           ,[Active]

           ,[Date]

           ,[EditedBy]

           ,[DateAdded]

           ,[CompanyID])

		 VALUES

           (@SerialNumber

           ,@Invoice

           ,@Inspection

           ,@PO

           ,'True'

           ,@Date

           ,@EditedBy

           ,@DateAdded

           ,@RetID)

           

           

	END

	ELSE

	BEGIN

		--Insert New Company

		INSERT INTO [lxp_1z3etnasf_site_aplus_net].[dbo].[Company]

           ([Name])

		VALUES

           (@Company)

		--Get just created Company id

		SELECT @RetID = [ID] FROM [lxp_1z3etnasf_site_aplus_net].[dbo].[Company] WHERE [Name] = @Company

		--Insert Login and link to Company table

		INSERT INTO [lxp_1z3etnasf_site_aplus_net].[dbo].[Certifications]

           ([Serial Number]

           ,[Invoice Authorization]

           ,[Inspection Number]

           ,[PO Number]

           ,[Active]

           ,[Date]

           ,[EditedBy]

           ,[DateAdded]

           ,[CompanyID])

		 VALUES

           (@SerialNumber

           ,@Invoice

           ,@Inspection

           ,@PO

           ,'True'

           ,@Date

           ,@EditedBy

           ,@DateAdded

           ,@RetID)

           

           

	END

	@RetVal INT OUTPUT

	END

Open in new window

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 22

Expert Comment

by:pivar
ID: 33550118
Hi,

How about:


USE [lxp_1z3etnasf_site_aplus_net]
GO
/****** Object:  StoredProcedure [dbo].[sp_AddRecord]    Script Date: 08/27/2010 17:45:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:              Roy Miller
-- Create date: 12/1/2009
-- Description: Add New Record
-- =============================================
ALTER PROCEDURE [dbo].[sp_AddRecord] 
        (
                @SerialNumber varchar(50)= NULL,
                @Company varchar(50)= NULL,
                @Invoice varchar(50)= NULL,
                @Inspection varchar(50)= NULL,
                @PO varchar(50) = NULL,
                @date datetime,
                @EditedBy varchar(50),
                @DateAdded datetime
        )
AS
BEGIN
        SET NOCOUNT ON;
        DECLARE @RetID As int;
        SET @RetID = 0;
        
        If Exists(Select 1 from [lxp_1z3etnasf_site_aplus_net].[dbo].[Company] WHERE [Name] = @Company)
        BEGIN
                --Get Id if it does exist
                SELECT @RetID = [ID] FROM [lxp_1z3etnasf_site_aplus_net].[dbo].[Company] WHERE [Name] = @Company
                --Insert Login and link to Company table
                INSERT INTO [lxp_1z3etnasf_site_aplus_net].[dbo].[Certifications]
           ([Serial Number]
           ,[Invoice Authorization]
           ,[Inspection Number]
           ,[PO Number]
           ,[Active]
           ,[Date]
           ,[EditedBy]
           ,[DateAdded]
           ,[CompanyID])
                 VALUES
           (@SerialNumber
           ,@Invoice
           ,@Inspection
           ,@PO
           ,'True'
           ,@Date
           ,@EditedBy
           ,@DateAdded
           ,@RetID)
           
           
        END
        ELSE
        BEGIN
                --Insert New Company
                INSERT INTO [lxp_1z3etnasf_site_aplus_net].[dbo].[Company]
           ([Name])
                VALUES
           (@Company)
                --Get just created Company id
                SELECT @RetID = [ID] FROM [lxp_1z3etnasf_site_aplus_net].[dbo].[Company] WHERE [Name] = @Company
                --Insert Login and link to Company table
                INSERT INTO [lxp_1z3etnasf_site_aplus_net].[dbo].[Certifications]
           ([Serial Number]
           ,[Invoice Authorization]
           ,[Inspection Number]
           ,[PO Number]
           ,[Active]
           ,[Date]
           ,[EditedBy]
           ,[DateAdded]
           ,[CompanyID])
                 VALUES
           (@SerialNumber
           ,@Invoice
           ,@Inspection
           ,@PO
           ,'True'
           ,@Date
           ,@EditedBy
           ,@DateAdded
           ,@RetID)
           
           
        END
        RETURN @RetID;
END

Open in new window

0
 
LVL 22

Assisted Solution

by:pivar
pivar earned 50 total points
ID: 33550241
Above would give you a return value. If you like an output parameter try this instead.
USE [lxp_1z3etnasf_site_aplus_net]
GO
/****** Object:  StoredProcedure [dbo].[sp_AddRecord]    Script Date: 08/27/2010 17:45:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:              Roy Miller
-- Create date: 12/1/2009
-- Description: Add New Record
-- =============================================
ALTER PROCEDURE [dbo].[sp_AddRecord] 
        (
                @SerialNumber varchar(50)= NULL,
                @Company varchar(50)= NULL,
                @Invoice varchar(50)= NULL,
                @Inspection varchar(50)= NULL,
                @PO varchar(50) = NULL,
                @date datetime,
                @EditedBy varchar(50),
                @DateAdded datetime,
                @RetId int OUTPUT
        )
AS
BEGIN
        SET NOCOUNT ON;
        SET @RetID = 0;
        
        If Exists(Select 1 from [lxp_1z3etnasf_site_aplus_net].[dbo].[Company] WHERE [Name] = @Company)
        BEGIN
                --Get Id if it does exist
                SELECT @RetID = [ID] FROM [lxp_1z3etnasf_site_aplus_net].[dbo].[Company] WHERE [Name] = @Company
                --Insert Login and link to Company table
                INSERT INTO [lxp_1z3etnasf_site_aplus_net].[dbo].[Certifications]
           ([Serial Number]
           ,[Invoice Authorization]
           ,[Inspection Number]
           ,[PO Number]
           ,[Active]
           ,[Date]
           ,[EditedBy]
           ,[DateAdded]
           ,[CompanyID])
                 VALUES
           (@SerialNumber
           ,@Invoice
           ,@Inspection
           ,@PO
           ,'True'
           ,@Date
           ,@EditedBy
           ,@DateAdded
           ,@RetID)
           
           
        END
        ELSE
        BEGIN
                --Insert New Company
                INSERT INTO [lxp_1z3etnasf_site_aplus_net].[dbo].[Company]
           ([Name])
                VALUES
           (@Company)
                --Get just created Company id
                SELECT @RetID = [ID] FROM [lxp_1z3etnasf_site_aplus_net].[dbo].[Company] WHERE [Name] = @Company
                --Insert Login and link to Company table
                INSERT INTO [lxp_1z3etnasf_site_aplus_net].[dbo].[Certifications]
           ([Serial Number]
           ,[Invoice Authorization]
           ,[Inspection Number]
           ,[PO Number]
           ,[Active]
           ,[Date]
           ,[EditedBy]
           ,[DateAdded]
           ,[CompanyID])
                 VALUES
           (@SerialNumber
           ,@Invoice
           ,@Inspection
           ,@PO
           ,'True'
           ,@Date
           ,@EditedBy
           ,@DateAdded
           ,@RetID)
           
           
        END
END

Open in new window

0
 
LVL 51

Expert Comment

by:tedbilly
ID: 33550565
Using an IF statement in the stored procedure prevents SQL from reusing the cached execution plan which slows the server down.  I'd recommend the following:


USE [lxp_1z3etnasf_site_aplus_net]

GO

/****** Object:  StoredProcedure [dbo].[sp_AddRecord]    Script Date: 08/27/2010 17:45:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO







-- =============================================

-- Author:              Roy Miller

-- Create date: 12/1/2009

-- Description: Add New Record

-- =============================================

ALTER PROCEDURE [dbo].[sp_AddRecord] 

        (

                @SerialNumber varchar(50)= NULL,

                @Company varchar(50)= NULL,

                @Invoice varchar(50)= NULL,

                @Inspection varchar(50)= NULL,

                @PO varchar(50) = NULL,

                @date datetime,

                @EditedBy varchar(50),

                @DateAdded datetime,

                @RetId int OUTPUT

        )

AS

BEGIN

        SET NOCOUNT ON;

        

        SELECT @RetID = [ID] FROM [lxp_1z3etnasf_site_aplus_net].[dbo].[Company] WHERE [Name] = @Company



        -- If @RetID IS NULL then insert one

        INSERT INTO [lxp_1z3etnasf_site_aplus_net].[dbo].[Company]

               ([Name])

           SELECT (@Company) WHERE @RetID IS NULL



        -- NOTE: Microsoft recommends using SCOPE_IDENTITY instead of @@IDENTITY

        SELECT @RetID = ISNULL(@RetID, SCOPE_IDENTITY())



        --Insert Login and link to Company table

        INSERT INTO [lxp_1z3etnasf_site_aplus_net].[dbo].[Certifications]

           ([Serial Number]

           ,[Invoice Authorization]

           ,[Inspection Number]

           ,[PO Number]

           ,[Active]

           ,[Date]

           ,[EditedBy]

           ,[DateAdded]

           ,[CompanyID])

                 VALUES

           (@SerialNumber

           ,@Invoice

           ,@Inspection

           ,@PO

           ,'True'

           ,@Date

           ,@EditedBy

           ,@DateAdded

           ,@RetID)

           

        RETURN 0

END

Open in new window

0
 

Author Comment

by:techpr0
ID: 33551075
I tried the last 3 solutions, but they give me the ID field of the Company table. I need the primary key of the Certification table (certID).

I will still be using the @RetID to link the tables.

Thanks for the help so far.
0
 

Author Comment

by:techpr0
ID: 33551310
I got it to produce the correct info. like this, but is there a better way to write it for my application or is this ok.
USE [lxp_1z3etnasf_site_aplus_net]

GO

/****** Object:  StoredProcedure [dbo].[sp_AddRecord1]    Script Date: 08/28/2010 18:09:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO







-- =============================================

-- Author:              

-- Create date: 12/1/2009

-- Description: Add New Record

-- =============================================

ALTER PROCEDURE [dbo].[sp_AddRecord1] 

        (

                @SerialNumber varchar(50)= NULL,

                @Company varchar(50)= NULL,

                @Invoice varchar(50)= NULL,

                @Inspection varchar(50)= NULL,

                @PO varchar(50) = NULL,

                @date datetime,

                @EditedBy varchar(50),

                @DateAdded datetime

        )

AS

BEGIN

        SET NOCOUNT ON;

        DECLARE @RetID As int;

        SET @RetID = 0;

        

        If Exists(Select 1 from [lxp_1z3etnasf_site_aplus_net].[dbo].[Company] WHERE [Name] = @Company)

        BEGIN

                --Get Id if it does exist

                SELECT @RetID = [ID] FROM [lxp_1z3etnasf_site_aplus_net].[dbo].[Company] WHERE [Name] = @Company

                --Insert Login and link to Company table

                INSERT INTO [lxp_1z3etnasf_site_aplus_net].[dbo].[Certifications]

           ([Serial Number]

           ,[Invoice Authorization]

           ,[Inspection Number]

           ,[PO Number]

           ,[Active]

           ,[Date]

           ,[EditedBy]

           ,[DateAdded]

           ,[CompanyID])

                 VALUES

           (@SerialNumber

           ,@Invoice

           ,@Inspection

           ,@PO

           ,'True'

           ,@Date

           ,@EditedBy

           ,@DateAdded

           ,@RetID)

           

           SELECT @@identity AS newID

        END

        ELSE

        BEGIN

                --Insert New Company

                INSERT INTO [lxp_1z3etnasf_site_aplus_net].[dbo].[Company]

           ([Name])

                VALUES

           (@Company)

                --Get just created Company id

                SELECT @RetID = [ID] FROM [lxp_1z3etnasf_site_aplus_net].[dbo].[Company] WHERE [Name] = @Company

                --Insert Login and link to Company table

                INSERT INTO [lxp_1z3etnasf_site_aplus_net].[dbo].[Certifications]

           ([Serial Number]

           ,[Invoice Authorization]

           ,[Inspection Number]

           ,[PO Number]

           ,[Active]

           ,[Date]

           ,[EditedBy]

           ,[DateAdded]

           ,[CompanyID])

                 VALUES

           (@SerialNumber

           ,@Invoice

           ,@Inspection

           ,@PO

           ,'True'

           ,@Date

           ,@EditedBy

           ,@DateAdded

           ,@RetID)

           

           SELECT @@identity AS newID

        END

        

END

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33551469
Unless you are still using SQL Server 7 do not use @@IDENTITY and instead use SCOPE_IDENTITY()
0
 
LVL 51

Assisted Solution

by:tedbilly
tedbilly earned 75 total points
ID: 33551629
OK, based on your last comment use this:
USE [lxp_1z3etnasf_site_aplus_net]

GO

/****** Object:  StoredProcedure [dbo].[sp_AddRecord]    Script Date: 08/27/2010 17:45:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO







-- =============================================

-- Author:              Roy Miller

-- Create date: 12/1/2009

-- Description: Add New Record

-- =============================================

ALTER PROCEDURE [dbo].[sp_AddRecord] 

        (

                @SerialNumber varchar(50)= NULL,

                @Company varchar(50)= NULL,

                @Invoice varchar(50)= NULL,

                @Inspection varchar(50)= NULL,

                @PO varchar(50) = NULL,

                @date datetime,

                @EditedBy varchar(50),

                @DateAdded datetime,

                @CertId int OUTPUT

        )

AS

BEGIN

        SET NOCOUNT ON;

        

        DECLARE @companyID



        SELECT @companyID = [ID] FROM [lxp_1z3etnasf_site_aplus_net].[dbo].[Company] WHERE [Name] = @Company



        -- If @companyID IS NULL then insert one

        INSERT INTO [lxp_1z3etnasf_site_aplus_net].[dbo].[Company]

               ([Name])

           SELECT (@Company) WHERE @companyID IS NULL



        -- NOTE: Microsoft recommends using SCOPE_IDENTITY instead of @@IDENTITY

        SELECT @companyID = ISNULL(@companyID, SCOPE_IDENTITY())



        --Insert Login and link to Company table

        INSERT INTO [lxp_1z3etnasf_site_aplus_net].[dbo].[Certifications]

           ([Serial Number]

           ,[Invoice Authorization]

           ,[Inspection Number]

           ,[PO Number]

           ,[Active]

           ,[Date]

           ,[EditedBy]

           ,[DateAdded]

           ,[CompanyID])

                 VALUES

           (@SerialNumber

           ,@Invoice

           ,@Inspection

           ,@PO

           ,'True'

           ,@Date

           ,@EditedBy

           ,@DateAdded

           ,@companyID)



        SELECT @CertId = SCOPE_IDENTITY()

           

        RETURN 0

END

Open in new window

0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
execute a MS SQL script as a schedule SQL job 72 101
null value 15 70
SQL Server memory Issue 7 76
Calculating Business Hours 19 65
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now