locked
Return value from stored procedure RRS feed

  • 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





    Saturday, April 17, 2021 9:14 AM

Answers

  • User-265130869 posted

    It wasn't a sql error but an dapper issue

    • Marked as answer by Anonymous Tuesday, September 28, 2021 12:00 AM
    Saturday, April 17, 2021 2:33 PM