SQLReader doesn't return any rows...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQLReader doesn't return any rows...

Author

Topic

Wolven
Starting Member

4 Posts

Posted - 2014-12-01 : 19:13:31

I'm hoping one of you experts can tell me why the following SP doesn't return any rows from the last SELECT when ran using ExecuteReader. 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.

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-01 : 20:10:04

I'm not a programmer, so I don't have the answer regarding the SQLReader, but the stored proc needs some work. The transaction is unnecessary here. I wouldn't bother checking if the row exists already; handle the PK violation exception instead.


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 @ErrMsg varchar(3000)
       ,@ErrSeverity int
       ,@ErrState int;

BEGIN TRY	
	INSERT INTO [File]
	(
		[Key],
		Field1,
		Field2,
		TCd
	) 
	VALUES 
	(
		@Key,
		@Field1,
		@Field2,
		1
	);

	SELECT * 
	FROM [File]
	WHERE [Key] = @Key;
END TRY
BEGIN CATCH
    SELECT  @ErrSeverity = ERROR_SEVERITY(),
            @ErrState = ERROR_STATE(),
            @ErrMsg = @ErrMsg + '. ' + ERROR_MESSAGE() + '. Error in line ' + CAST(ERROR_LINE() AS varchar(1000)) + ' of procedure ' + ERROR_PROCEDURE();

    RAISERROR(@ErrMsg, @ErrSeverity, @ErrState);

    SET @RetCode = 2

    RETURN (1);
END CATCH;

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Wolven
Starting Member

4 Posts

Posted - 2014-12-02 : 03:33:34

Thanx Tara for the SQL advice. I currently do the Try Catch for any SQL errors in the program but maybe it would be better in the SP. I still don't understand why it doesn't return the inserted row.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-12-02 : 10:02:19

The reason you don't get the result from the final SELECT is because of the existence of the first SELECT (the one immediately after the BEGIN TRAN statement in your original code). So you should either eliminate that statement, or use the NextResult property of the SqlReader class to move to the second result set to get the results from the final select.

If you do want to eliminate the first select, do it like this:

IF NOT EXISTS
(
	SELECT * FROM [File] WHERE [Key] = @Key
)
BEGIN
	INSERT INTO [File]
	(
		[Key],
		Field1,
		Field2,
		TCd
	) 
	VALUES 
	(
		@Key,
		@Field1,
		@Field2,
		1
	);
	
	SELECT * FROM [File]
	WHERE ([Key] = @Key)
END
ELSE
BEGIN
	SET @RetCode = 2;
END

And, as suggested previously, there is no need for a transaction.

Wolven
Starting Member

4 Posts

Posted - 2014-12-02 : 18:04:18

Thanx James, that accomplished what I was after... But just out of curiosity, why is there no need for a transaction? The only reason I put it in there was in case another user happened to perfectly time an attempt to add the same record (and hence run the same SP). My understanding was the Transaction statements would keep any other SP from altering the Table DURING the processing of this SP. Is that not correct? Or does SQL automatically eliminate that possibility?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-02 : 18:13:38

Your INSERT is still inside a transaction even though you don't explicitly state it. It's called an implicit transaction and is done when there's a single DML command.

Regarding your question about two users simultaneously doing the same insert, that's not possible if you have a PRIMARY KEY constraint. Is KEY defined as the PK?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Wolven
Starting Member

4 Posts

Posted - 2014-12-03 : 01:49:21

Yes, the Key is a PK. So, does the Transaction statement slow the process down any, or create any additional locks over the "implicit" transaction processing?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-03 : 12:17:52

quote:
Originally posted by Wolven

Yes, the Key is a PK. So, does the Transaction statement slow the process down any, or create any additional locks over the "implicit" transaction processing?

You can leave the transaction in there, but I like clean code. Keeping it in there and taking it out will have the same effect.

Since Key is the PK, it's not possible for two users to insert the same Key. One will succeed, the other will throw a PK violation exception.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources