Return value from stored procedure

Question

User-265130869 posted

There is something I don't understand. In my StoredProcedure I want to return the Id value, but gets an -1 each time. The category are saved correctly to the database, but I get a wrong Id back.

When running the sp in Sql Server Management Studio, it gives me 2 output  26 in @Id and in Return Value a 0. And a little bit funny (to me) there is 2 selects in the Results. 

So I hope one of you can see, where I am doing something wrong

Best regards
Simsen :-)

USE [AnsiBug]
GO
/****** Object:  StoredProcedure [dbo].[Category_New]    Script Date: 17-04-2021 11:02:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Category_New]
	@CategoryName nvarchar(250),
	@CategoryIsGlobal bit,
	@CategoryIsObsolete bit,
	@ProjectId int,
	@Id int = 0 output
AS
BEGIN
	SET NOCOUNT ON;

	if @ProjectId = 0 
	set	@ProjectId = null

	INSERT INTO dbo.Category (CategoryName, CategoryIsGlobal, CategoryIsObsolete, ProjectId)
	values (@CategoryName, @CategoryIsGlobal, @CategoryIsObsolete, @ProjectId);

	select @Id = SCOPE_IDENTITY();

    
END

The output when running the sp

USE [AnsiBug]
GO

DECLARE	@return_value int,
		@Id int

EXEC	@return_value = [dbo].[Category_New]
		@CategoryName = N'test4',
		@CategoryIsGlobal = false,
		@CategoryIsObsolete = false,
		@ProjectId = 0,
		@Id = @Id OUTPUT

SELECT	@Id as N'@Id'

SELECT	'Return Value' = @return_value

GO