June 28, 2011 at 3:54 pm
---My procedure appears to work - If there is a record already in the table STORAGE and i try to run the stored procedure again it does give me the error primary key voliation error.
The question I have is I would like to capture a value for the @Success so that I know when to call the next stored procedure.
Upon checking if invalid record i cannot get it to return anything to me. I tired IF (XACT_STATE()) = 1
and @@transcount = 0 . What do i do to send back a @Success when no record inserted.
Cheers
CREATE PROCEDURE [dbo].[usp_ADD_APPLICATION_STORAGE] @STORAGE_LOCATION nvarchar(MAX), @Success int output
AS
BEGIN
declare @LOG TABLE (RowID INT IDENTITY, STORAGE_LOCATION nvarchar(MAX))
--Set NoCount On
Declare @ErrorLogID Int, @STORAGE_ID nvarchar(10)
Set @Success = 0
Begin Try
Begin
Begin Transaction
SELECT @STORAGE_ID = CONTROL_TABLE_ID + 1 FROM APPLICATION_CONTROL WHERE CONTROL_TABLE_NAME = 'STORAGE'
INSERT INTO STORAGE
(STORAGE_ID,STORAGE_LOCATION)
OUTPUT INSERTED.STORAGE_LOCATION INTO @LOG
SELECT @STORAGE_ID, @STORAGE_LOCATION
WHERE @STORAGE_LOCATION NOT IN (SELECT A.STORAGE_LOCATION FROM STORAGE A )
Commit Transaction
End
End Try
Begin Catch
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage
--Check XACT_STATE for 1 or -1
-- Test XACT_STATE for 1 or -1.
-- XACT_STATE = 0 means there is no transaction and
-- a commit or rollback operation would generate an error.
-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
SELECT @SUCCESS = 2
--The transaction is in an uncommittable state. ' + 'Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
SELECT @SUCCESS = 3
---'The transaction is committable. ' + 'Committing transaction.'
COMMIT TRANSACTION;
END;
--Set @Success = 0
End Catch
Return
END
-----calling function
declare @oParam3 int
---Storage
--Add the record to the storage
EXEC usp_ADD_APPLICATION_STORAGE '\\TEST', @oParam3 OUTPUT
--Tables used
--TABLE
CREATE TABLE [dbo].[STORAGE](
[STORAGE_ID] [nvarchar](10) NOT NULL,
[STORAGE_LOCATION] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_STORAGE] PRIMARY KEY CLUSTERED
(
[STORAGE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 72) ON [PRIMARY]
) ON [PRIMARY]
GO
--Control Table
CREATE TABLE [dbo].[APPLICATION_CONTROL](
[CONTROL_ID] [int] IDENTITY(1,1) NOT NULL,
[CONTROL_TABLE_NAME] [nvarchar](30) NULL,
[CONTROL_TABLE_ID] [nvarchar](10) NULL
) ON [PRIMARY]
GO
June 28, 2011 at 4:24 pm
The PK violation is error severity 14 - it's a correctable error that leaves the XACT_STATE() value at 1.
The better colution is to not program by exception, to not use an error to provide information. Errors should generally be errors.
A primary key violation is something that is entirely preventable. Testing for one before attempting the insert will put you in a situation where you can take whatever action you wish outside of a CATCH block:
IF NOT EXISTS(SELECT * FROM Storage WHERE Storage_ID = @Storage_ID)
AND NOT EXISTS(SELECT * FROM Storage WHERE Storage_Location = @Storage_Location)
INSERT STORAGE(STORAGE_ID,STORAGE_LOCATION)
OUTPUT INSERTED.STORAGE_LOCATION INTO @LOG
SELECT @STORAGE_ID, @STORAGE_LOCATION
ELSE
BEGIN
-- the row was not insertable
-- handle transactional stuff, set @Success value, etc.
END
-Eddie
Eddie Wuerch
MCM: SQL
June 28, 2011 at 4:25 pm
I'm not clear what's an invalid record that doesn't get caught. You aren't checking for xact_state = 0, which is a valid value.
The way you're doing this isn't what I'd normally do. I would make sure that no matter what I set a value, so in the last IF, I'd have an ELSE that set @success to 4 or some other state.
Can you show some sample data that you're testing and what isn't working? It's not quite clear in your explanation.
June 29, 2011 at 12:46 am
The following example shows your code rewritten to use appropriate types and to be robust under high concurrency conditions. The return code from the procedure is the number of rows written, so zero indicates failure. You don't need an explicit transaction or TRY...CATCH:
USE tempdb;
GO
CREATE TABLE dbo.Storage
(
storage_idINTEGER NOT NULL,
storage_location NVARCHAR(350) NOT NULL,
CONSTRAINT [PK dbo.Storage storage_id]
PRIMARY KEY CLUSTERED (storage_id),
CONSTRAINT [UQ dbo.Storage storage_location]
UNIQUE (storage_location)
);
GO
CREATE TABLE dbo.ApplicationControl
(
table_name NVARCHAR(30) NOT NULL,
next_id INTEGER NOT NULL DEFAULT (0),
CONSTRAINT [PK dbo.ApplicationControl]
PRIMARY KEY CLUSTERED (table_name)
);
CREATE PROCEDURE dbo.AddApplicationStorage
(
@storage_location NVARCHAR(350)
)
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.Storage
(storage_id, storage_location)
SELECT
u.next_id, @storage_location
FROM
(
UPDATE dbo.ApplicationControl
SET next_id += 1
OUTPUT DELETED.next_id
WHERE
table_name = N'storage'
AND NOT EXISTS
(
SELECT
1
FROM dbo.Storage AS s WITH (UPDLOCK, HOLDLOCK)
WHERE
s.storage_location = @storage_location
)
) AS u;
RETURN @@ROWCOUNT;
END;
INSERT dbo.ApplicationControl
(table_name, next_id)
VALUES
(N'storage', 1);
GO
-- Test the procedure
DECLARE @rc INTEGER;
EXECUTE @rc = dbo.AddApplicationStorage
@storage_location = N'\\Test';
SELECT @rc AS return_code;
GO
-- Show the contents of the tables
SELECT * FROM dbo.ApplicationControl AS ac
SELECT * FROM dbo.Storage AS s
GO
GO
-- Tidy up
DROP TABLE dbo.Storage
DROP TABLE dbo.ApplicationControl
DROP PROCEDURE dbo.AddApplicationStorage
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 29, 2011 at 7:10 am
That looks good i will look at this and work with it. Thank you for taking the time to review.
June 30, 2011 at 1:40 pm
Paul,
That one is bloody brilliant! It would never have occurred to me to use an OUTPUT clause from an UPDATE for an INSERT.
Remind me to buy you a beer if we ever meet.
Todd Fifield
June 30, 2011 at 2:13 pm
tfifield (6/30/2011)
That one is bloody brilliant! It would never have occurred to me to use an OUTPUT clause from an UPDATE for an INSERT.
:blush: Thank you.
Remind me to buy you a beer if we ever meet.
Deal!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 3, 2011 at 5:43 pm
tfifield (6/30/2011)
Paul,That one is bloody brilliant! It would never have occurred to me to use an OUTPUT clause from an UPDATE for an INSERT.
Remind me to buy you a beer if we ever meet.
Todd Fifield
Taking notes...I want in on the 'answer = beer' aspect of SSC 🙂
Eddie Wuerch
MCM: SQL
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply