SQL variable in nested stored procedure

Question

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

When I call this stored procedure separately, it works fine. But when I am nested in another stored procedure, the value is not set to the @TempLastGeneratedNumber variable. When I called the stored procedure in another procedure, and if I print the value of @TempLastGeneratedNumber , it is empty.

But when I call the stored procedure separately, 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 with what the problem is in the stored procedure described above and how to solve it?

Answer 1

The value is NULL, most likely because queries do not return any value or return NULL.

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

Try printing the argument values ​​when calling the procedure.

Another possibility is that you did not declare @FinalNumber as the output parameter for the call, as well as for the definition.

I really think it's a bad practice to name a variable something like "any NUMBER", and then its type is not numeric. However, this is not my cause of your problem.