techpr0
asked on
Stored procedure. Insert then return primary key.
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.
The primary key is an integer that autofills in increments.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
I'm using this to get the returned value in the application.
Dim strCert As String = command.Parameters("RetVal
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
Add one output parameter to your stored procedure
@RetVal INT OUTPUT
And remove its declaration inside Stored procedure
Raj
@RetVal INT OUTPUT
And remove its declaration inside Stored procedure
Raj
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Changed to the following based on your previous response, but i get an error.
Incorrect Syntax near '@RetVal'
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
Hi,
How about:
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
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.
I will still be using the @RetID to link the tables.
Thanks for the help so far.
ASKER
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
Unless you are still using SQL Server 7 do not use @@IDENTITY and instead use SCOPE_IDENTITY()
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Raj
Open in new window