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

by

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'
*/

CREATE PROCEDURE [dbo].[SSRS_Call_BOM] 
    (@DueDate DateType="2023-10-01")
AS
    EXEC [dbo].[SP_GenerateBOM] 
        '9370-01270-004','9370-01270-004',null,null,'MTFO','PMT','B'
        ,'ABC','2019-04-11',0,0,0,0,0

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

EXEC [dbo].[SP_GenerateBOM] 
     '9370-01270-004','9370-01270-004',null,null,'MTFO','PMT','B','ABC','2019-04-11',0,0,0,0,0`

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