I answered the following question earlier this evening: Return an output parameter from SQL Server via a stored procedure and c#

Here is the proc in question, take a good look at it, do you see the problem?

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
ALTER PROCEDURE [dbo].[Insert_UnknownCustomer_Quote_Document]
-- Add the parameters for the stored procedure here
@NewDocumentFileName nvarchar(100),
@NewDocumentWordCount int,
@NewQuoteAmount money,
@NewQuoteNumber int OUTPUT = 0
 
AS
 
DECLARE @Today datetime
SELECT @Today = GETDATE()
 
BEGIN TRANSACTION
BEGIN TRY
 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
 
 
-- Insert statements for procedure here
INSERT INTO dbo.Customers(DateAdded)
VALUES (@Today)
 
INSERT INTO dbo.Quotes(CustomerID, QuoteAmount, QuoteDate)
VALUES (@@IDENTITY, @NewQuoteAmount, @Today)
 
SELECT @NewQuoteNumber = @@IDENTITY
INSERT INTO dbo.DocumentFiles(QuoteNumber, DocumentFileName, DocumentFileWordCount)
VALUES (@NewQuoteNumber, @NewDocumentFileName, @NewDocumentWordCount)
 
-- Return quote number
RETURN @NewQuoteNumber
 
END
COMMIT TRANSACTION
END TRY
 
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT 'Transaction rolled back.'
END CATCH
ALTER PROCEDURE [dbo].[Insert_UnknownCustomer_Quote_Document]
-- Add the parameters for the stored procedure here
@NewDocumentFileName nvarchar(100),
@NewDocumentWordCount int,
@NewQuoteAmount money,
@NewQuoteNumber int OUTPUT = 0

AS

DECLARE @Today datetime
SELECT @Today = GETDATE()

BEGIN TRANSACTION
BEGIN TRY

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;


-- Insert statements for procedure here
INSERT INTO dbo.Customers(DateAdded)
VALUES (@Today)

INSERT INTO dbo.Quotes(CustomerID, QuoteAmount, QuoteDate)
VALUES (@@IDENTITY, @NewQuoteAmount, @Today)

SELECT @NewQuoteNumber = @@IDENTITY
INSERT INTO dbo.DocumentFiles(QuoteNumber, DocumentFileName, DocumentFileWordCount)
VALUES (@NewQuoteNumber, @NewDocumentFileName, @NewDocumentWordCount)

-- Return quote number
RETURN @NewQuoteNumber

END
COMMIT TRANSACTION
END TRY

BEGIN CATCH
ROLLBACK TRANSACTION
PRINT 'Transaction rolled back.'
END CATCH

Here is a simplified version

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
CREATE proc prTest
as
DECLARE @id int
 
BEGIN TRAN
    INSERT TestID DEFAULT VALUES
 
    SELECT @id  =SCOPE_IDENTITY()
 
    RETURN @id
COMMIT TRAN
GO
CREATE proc prTest
as
DECLARE @id int

BEGIN TRAN
	INSERT TestID DEFAULT VALUES

	SELECT @id  =SCOPE_IDENTITY()

	RETURN @id
COMMIT TRAN
GO

Do you see it?

Let’s see what happens when you try running it

First create this table

T-SQL
1
2
CREATE TABLE TestID (id int identity)
go
CREATE TABLE TestID (id int identity)
go

Here is the proc again

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
CREATE proc prTest
as
DECLARE @id int
 
BEGIN TRAN
    INSERT TestID DEFAULT VALUES
 
    SELECT @id  =SCOPE_IDENTITY()
 
    RETURN @id
COMMIT TRAN
GO
CREATE proc prTest
as
DECLARE @id int

BEGIN TRAN
	INSERT TestID DEFAULT VALUES

	SELECT @id  =SCOPE_IDENTITY()

	RETURN @id
COMMIT TRAN
GO

Go ahead and execute it

T-SQL
1
2
3
DECLARE @id int
EXEC @id = prTest
SELECT @id
DECLARE @id int
EXEC @id = prTest
SELECT @id

Msg 266, Level 16, State 2, Procedure prTest, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

So the stored procedure blew up, no big deal right?
Open a new query window, run this

T-SQL
1
SELECT * FROM TestID
SELECT * FROM TestID

Take a note of the SPID in the status bar next to the username

As you can see the query is stuck
Now open yet another window and execute this

T-SQL
1
2
3
SELECT blocking_session_id,* 
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
SELECT blocking_session_id,* 
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0

You will see that the SPID of the session where the select is running is returned by this query

As you can see, the transaction is still active and not committed, this is why the select statement is blocked

Go back to the first query window and either run COMMIT or ROLLBACK, the query will finish now

In the stored procedure, the return statement should come after the commit, it should look like this

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
ALTER proc prTest
as
DECLARE @id int
 
BEGIN TRAN
    INSERT TestID DEFAULT VALUES
 
    SELECT @id  =SCOPE_IDENTITY()
 
 
COMMIT TRAN
RETURN @id
GO
ALTER proc prTest
as
DECLARE @id int

BEGIN TRAN
	INSERT TestID DEFAULT VALUES

	SELECT @id  =SCOPE_IDENTITY()


COMMIT TRAN
RETURN @id
GO

Now, you won’t get an error or a hanging transaction. Run it again

T-SQL
1
2
3
DECLARE @id int
EXEC @id = prTest
SELECT @id
DECLARE @id int
EXEC @id = prTest
SELECT @id

It works now

In general, I don’t like to use return statements to return IDs, I like to use OUTPUT parameters instead, return statements in my opinion are to be used to return a status code

Here is what the proc would look like with an OUTPUT parameter

T-SQL
1
2
3
4
5
6
7
8
9
ALTER proc prTest @id int OUTPUT
AS
 
BEGIN TRAN
    INSERT TestID DEFAULT VALUES
 
    SELECT @id  =SCOPE_IDENTITY()
COMMIT TRAN
GO
ALTER proc prTest @id int OUTPUT
AS

BEGIN TRAN
	INSERT TestID DEFAULT VALUES

	SELECT @id  =SCOPE_IDENTITY()
COMMIT TRAN
GO

Here is how you call it

T-SQL
1
2
3
DECLARE @id int
EXEC  prTest @id output
SELECT @id
DECLARE @id int
EXEC  prTest @id output
SELECT @id

Do you use a return statement to return identity values or do you use output parameters or do you use a plain vanilla SELECT statement? Leave me a comment and let me know