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