Background:
I have a series of stored procedures that inserts one new record into 5 tables (all have a one-to-one relationship). Each stored procedure creates a unique ID for the related table. The last stored procedure adds all of those unique IDs into the last table (to allow for an INNER JOIN to retrieve all information across the individual tables).
I have created an additional stored procedure (let's call it spWrapper) that calls the 4 individual stored procedures (mentioned above). I would like to use the spWrapper to both insert and update a record. The current (insert) spWrapper only inserts a record; the 5 unique IDs are declared as OUTPUT parameters. My T-SQL knowledge is still basic and I am not sure how OUTPUT parameters affect stored procedures.
The current spWrapper code (shortened example):
ALTER PROCEDURE [dbo].[spWrapper]
-- Return values
@idAddress INT = NULL OUTPUT,
@idDetermination INT = NULL OUTPUT,
@idLegalDescription INT = NULL OUTPUT,
@idAddresses_LegalDescriptions_Determinations INT = NULL OUTPUT,
@idLOMC INT = NULL OUTPUT,
-- [Table#1] parameters
@Street VARCHAR(50) = NULL,
@City VARCHAR(50) = NULL,
@State VARCHAR(2) = NULL,
@ZipCode5 VARCHAR(5) = NULL,
@ZipCode4 VARCHAR(4) = NULL,
@GISCode VARCHAR(15) = NULL
AS
SET NOCOUNT OFF
SET ROWCOUNT 0
-- =================================
-- Declare and initialize variables
-- =================================
DECLARE @Error INT,
@RC INT,
@Trancount INT,
@Message VARCHAR(255)
SELECT @Error = 0,
@RC = 0,
@Trancount = @@TRANCOUNT,
@Message = NULL
-- ==========================================
-- Insert record into [Table#1]
-- ==========================================
IF @idAddress IS NULL
BEGIN
IF @Trancount = 0 BEGIN TRANSACTION
EXEC @RC = [spTable#1]
@idLogin = @idLogin,
@idAddress = @idAddress,
@Street = @Street,
@State = @State,
@City = @City,
@ZipCode5 = @ZipCode5,
@ZipCode4 = @ZipCode4,
@GISCode = @GISCode
SELECT @Error = @@ERROR
IF @RC <> 0 OR @Error <> 0
BEGIN
IF @Trancount = 0 ROLLBACK TRANSACTION
SELECT @Message = 'dbo.spWrapper: Error inserting record into [Table#1]'
GOTO lbl_abort
END
END
IF @Trancount = 0 COMMIT TRANSACTION
The current spTable#1 code
ALTER PROCEDURE [spTable#1]
@idLogin INT,
@idAddress INT = NULL OUTPUT,
@Street VARCHAR(50),
@State CHAR(2),
@City VARCHAR(50),
@ZipCode5 CHAR(5),
@ZipCode4 CHAR(4),
@GisCode VARCHAR(15)
IF @TranCount = 0 BEGIN TRANSACTION
IF @idAddress IS NULL
BEGIN
INSERT [dbo].[Table#1]
(Street,
[State],
City,
ZipCode5,
ZipCode4,
GisCode,
InsertedidLogin,
InsertedDate)
VALUES
(@Street,
@State,
@City,
@ZipCode5,
@ZipCode4,
@GisCode,
@idLogin,
GETDATE())
SELECT @Error = @@ERROR,
@RC = @@ROWCOUNT,
@idAddress = SCOPE_IDENTITY()
IF @Error <> 0 OR @RC <> 1
BEGIN
IF @TranCount = 0 ROLLBACK TRANSACTION
SELECT @Message = 'spTable#1: Error inserting record into [Table#1]'
GOTO lbl_abort
END
END
My main question:
With the unique ID set as an OUTPUT parameter, can I insert an existing ID to call an UPDATE portion of spTable#1 (and subsequently spWrapper)? OR can I declare those unique IDs (ie: not as an OUTPUT parameter)?
Proposed change
CREATE PROCEDURE [dbo].[spWrapper]
@idLOMC INT = NULL
DECLARE @idAddress INT,
@idLegalDescription INT,
@idDetermination INT,
@idCommunity INT,
@idFirm INT,
@idCounty INT,
@idFloodZone INT,
@Error INT,
@RC INT,
@Trancount INT,
@Message VARCHAR(255)
SELECT @idAddress = NULL,
@idLegalDescription = NULL,
@idDetermination = NULL,
@Error = 0,
@RC = 0,
@Trancount = @@TRANCOUNT,
@Message = NULL
/* Lookup idAddress, idLegalDescription, idDetermination */
IF @idLOMC IS NOT NULL
BEGIN
SELECT @idAddress = A.idAddress, @idLegalDescription = LD.idLegalDescription, @idDetermination = D.idDetermination
FROM [Table#2] L
INNER JOIN [Table#5] ALD
ON L.idAddresses_LegalDescriptions_Determinations = ALD.idAddresses_LegalDescriptions_Determinations
INNER JOIN [Table#1] A
ON ALD.idAddress = A.idAddress
INNER JOIN [Table#3] LD
ON ALD.idLegalDescription = LD.idLegalDescription
INNER JOIN [Table#4] D
ON ALD.idDetermination = D.idDetermination
WHERE L.idLOMC = @idLOMC
IF @@ROWCOUNT = 0
BEGIN
SELECT @Message = 'dbo.spWrapper: Invalid idLOMC'
GOTO lbl_abort
END
END
/* Insert record into [Table#1] */
IF @idAddress IS NULL
BEGIN
IF @Trancount = 0 BEGIN TRANSACTION
EXEC @RC = [spTable#1]
@idLogin = @idLogin,
@idAddress = @idAddress OUTPUT,
@Street = @Street,
@State = @State,
@City = @City,
@ZipCode5 = @ZipCode5,
@ZipCode4 = @ZipCode4,
@GisCode = @GisCode
SELECT @Error = @@ERROR
IF @RC <> 0 OR @Error <> 0
BEGIN
IF @Trancount = 0 ROLLBACK TRANSACTION
SELECT @Message = 'spWrapper: Error inserting record into [Table#1]'
GOTO lbl_abort
END
END
/* Update record into [Table#1] */
ELSE
BEGIN
IF @Trancount = 0 BEGIN TRANSACTION
EXEC @RC = [spTable#1]
@idLogin = @idLogin,
@idAddress = @idAddress,
@Street = @Street,
@State = @State,
@City = @City,
@ZipCode5 = @ZipCode5,
@ZipCode4 = @ZipCode4,
@GisCode = @GisCode
SELECT @Error = @@ERROR
IF @RC <> 0 OR @Error <> 0
BEGIN
IF @Trancount = 0 ROLLBACK TRANSACTION
SELECT @Message = 'spWrapper: Error updating record in [TspTable#1]'
GOTO lbl_abort
END
END
