嵌套存储过程中的SQL变量

I have created a stored procedure that retrieves last values from the specific column in a table and increments it by one based on type of document and location.

我创建了一个存储过程，该过程从表中的特定列检索最后一个值，并根据文档类型和位置将其递增1。

When I call this stored procedure in isolation it works perfectly. But when I nest it in another stored procedure, the value is not set to a variable @TempLastGeneratedNumber. When I called the stored procedure within another procedure and if I print the value of @TempLastGeneratedNumber it is empty.

当我单独调用这个存储过程时，它可以完美地工作。但是当我将它嵌套在另一个存储过程中时，该值不会设置为变量@TempLastGeneratedNumber。当我在另一个过程中调用存储过程并且如果我打印@TempLastGeneratedNumber的值时它是空的。

But when I call the stored procedure in isolation I get the correct value.

但是当我单独调用存储过程时，我得到了正确的值。

This is my stored procedure

这是我的存储过程

    Create Procedure [GenerateDocumentNo]
        @Document_Type varchar(max) = null,
        @Location_Id int = null
        @FinalNumber varchar(max) = null output
    as
    begin try
    begin tran

    Declare @TempLastGeneratedNumber varchar(max)

    if (@Document_Type = 'Apple')
    BEGIN
    Select top(1) @TempLastGeneratedNumber = Code from Apple_Details_tbl where Location_Id = @Location_Id  order by Id desc
    END

    else if (@Document_Type = 'Mango')
    BEGIN
    Select top(1) @TempLastGeneratedNumber = Code from Mango_Details_tbl where Location_Id = @Location_Id order by Id desc
    END


    if(@TempLastGeneratedNumber is not null or @TempLastGeneratedNumber != '')
    BEGIN

    Set @FinalNumber = @TempLastGeneratedNumber + 1
    END

    commit tran
    end try

    begin catch
    PRINT ERROR_MESSAGE()
    if
    end catch

So please help me what is the issue in above stored procedure and how to solve this?

那么请帮我解决上面存储过程中的问题以及如何解决这个问题？

1 个解决方案

#1

The value is NULL most likely because the queries are not returning any value or are returning a NULL value.

值很可能是NULL，因为查询没有返回任何值或返回NULL值。

Presumably, this is because @Document_Type is neither "Apple" nor "Mango". Or, because the location_id is not in the table. Or, because the code on the maximum id is NULL.

据推测，这是因为@Document_Type既不是“Apple”也不是“Mango”。或者，因为location_id不在表中。或者，因为最大id上的代码是NULL。

Try printing out the values of the arguments when you are calling the procedure.

在调用过程时尝试打印出参数的值。

The other possibility is that you have not declared @FinalNumber as an output parameter on the call as well as the definition.

另一种可能性是你没有将@FinalNumber声明为调用的输出参数以及定义。

I do think it is bad practice to name a variable something like "whatever NUMBER" and then have its type be non-numeric. However, I don't this is causing your problem.

我认为将变量命名为“无论NUMBER”然后将其类型设置为非数字是不好的做法。但是，我不这是导致你的问题。

#1