Executing a stored procedure from another stored procedure generates different record sets than executing it directly [closed]

I have a stored procedure for the application I am responsible for its data reporting. I intend to use it in another stored procedure that I write to compile data for a big report. I found out that executing this stored procedure [dbo].[SP_GenerateBOM] from another stored procedure results in very different record sets than executing it directly.

I have done a simple test using the following method, I created a separate stored procedure called [dbo].[SSRS_Call_BOM] like this:

EXEC  [dbo].[SSRS_Call_BOM]  '2023-10-01'

    (@DueDate DateType="2023-10-01")
    EXEC [dbo].[SP_GenerateBOM] 

When I execute the stored procedure directly, using this statement:

EXEC [dbo].[SP_GenerateBOM] 

it returns 105 records, which is correct and accurate.

However, if I execute the stored procedure from the other stored procedure using:

EXEC [dbo].[SSRS_Call_BOM] '2023-10-01'

it returns over 850 records, which is not correct.

This is the first time that I run into this type of strange behavior. How I should deal with this situation.

  • 8

    FYI the prefix sp_ is reserved, by Microsoft, for Special / System Procedures. It should not be used for User Procedures. Doing so comes with a performance cost and the risk of your Procedure simply not working one day after an update/upgrade. Either use a different prefix or (possibly better) no prefix at all. Is the sp_ prefix still a no-no?


  • 3

    Is the '2023-04-11' value being converted to or compared with a DATETIME type somewhere in your code? If you are working on a server where DMY dates are the norm, SQL Server sometimes interprets “xxxx-xx-xx” as “yyyy-dd-mm” instead of the intended “yyyy-mm-dd” (stupid design decision made decades ago). Perhaps one of the '2023-04-11' values is being converted as November 4 instead of the intended April 11. Try using '20230411' without dashes. That will always be interpreted as yyyymmdd.


  • 2

    Why does SSRS_Call_BOM have EXEC SP_GenerateBOM ... '2019-04-11' hard-coded, instead of passing @DueDate along? If you call the outer procedure with any other date, it will still call the inner procedure with '2019-04-01'. And agreed with the others – don’t use sp_ and don’t use yyyy-MM-dd.


  • 1

    We don’t know what SP_GenerateBOM does! it could easily check the caller (who runs it) and take a different execution path.


  • 1

    You need to create a DBFiddle demonstrating this issue – else its going to get closed as unreproducible.


Leave a Comment