December 1, 2014 at 4:21 pm
I'm hoping one of you SQL experts can tell me why the following stored procedure, when ran using ExecuteReader doesn't return any rows. It DOES add the record and I'm trapping any SQL errors (there aren't any). It just happily adds the record but apparently doesn't find it in the following SELECT statement. I'm wondering if it's a timing issue. If I run the same SELECT statement after the INSERT statement from the program, it does return the row. Also, I use the exact same SELECT statement right after an UPDATE in another SP, and it also returns the row.
Thanx.
/****** Object: StoredProcedure [dbo].[File_Add] Script Date: 12/01/2014 15:06:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[File_Add]
(
@RetCode int OUTPUT,
@ForceUpd int,
@InactiveCd int,
@key int,
@Field1 int,
@Field2 int,
@TCd int
)
AS SET NOCOUNT ON
DECLARE @Count int
SET @RetCode = 0
BEGIN TRANSACTION
SELECT TOP (1) * FROM File
WHERE (Key = @key)
Set @Count = @@ROWCOUNT
IF @Count <> 0
SET @RetCode = 2 -- Record already exists
ELSE -- IF @Count <> 0
BEGIN -- Add the Record
INSERT INTO File
(
Key,
Field1,
Field2,
TCd
)
VALUES
(
@key,
@Field1,
@Field2,
1
)
-- Now Retrieve the Inserted Record in case any default fields were set during the add
SELECT TOP (1) * FROM File
WHERE (Key = @key)
END -- End Add the Record
COMMIT TRANSACTION
December 2, 2014 at 2:02 am
Your procedure returns two result sets when the row is inserted. You may need to call SqlDataReader.NextResult() to read the second one.
December 2, 2014 at 2:15 am
I'm checking the Readers "HasRows" property after running the stored procedure, and it's false. Would the two different result sets have different HasRows values?
I'll give it a try anyway.
Thanx.
December 2, 2014 at 2:25 am
On a slightly different topic... your procedure has a race-condition. Two people run this at the same time with the same key, one to insert the key and the other to get a primary key/unique constraint violation (assuming there is a pk or constraint on Key)
I would suggest you change the entire SELECT ... IF .. INSERT ... into a single INSERT and then a SELECT to return the row regardless of whether it was just added or not. Makes it a lot more robust and less likely to cause odd errors under heavy concurrent load.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2014 at 4:11 pm
GilaMonster, Just to make sure I'm understanding what you're saying... in the event of two users concurrently trying to add the same row, the way the SP is written (Key is a PK) one of them will get an error since the row already exists when they try to add it? If that's what you're saying, then that's exactly what I want to happen. I handle the "can't add because of duplicate key" error in the program.
December 3, 2014 at 1:36 am
In that case, why are you even bothering to do the checks? Just do the insert and then run the select.
Generally, when someone writes code that:
- Checks if a row exists
- Inserts only if the check returns false (row does not exist)
then they're trying to ensure that primary key/unique constraint violations don't occur.
If you don't care, just write it as
INSERT INTO File (Key,Field1,Field2,TCd)
VALUES (@Key,@Field1,@Field2,1)
SELECT <column list> FROM File
WHERE Key = @key
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 3, 2014 at 1:37 pm
How about something like this:
DECLARE @ID INT, @DAT VARCHAR(100)
SET @ID = 1
SET @DAT = 'TEXT 1'
INSERT INTO TEST1
SELECT @ID, @DAT
WHERE NOT EXISTS (SELECT ID FROM TEST1 WHERE ID = @ID)
SELECT @@ROWCOUNT
I like it because it seems to rely on an implicit transaction to deal with races. Any drawbacks I don't know about?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply