SQL Server 2019: working with 'The rowset of the INSERT...EXEC statement is empty' scenarios with Stored Procedure
I've been researching this but I've spent hours debugging this and I've searched everywhere and can't find a clear answer....... I am working with an scenario when trying to execute an `INSERT...EXEC` statement in SQL Server 2019. The stored procedure I am calling is supposed to return a result set that I want to insert into another table, but I keep getting the behavior message: "The rowset of the INSERT...EXEC statement is empty." Here's the setup: I have a stored procedure called `GetEmployeeData` that fetches employee records based on certain criteria. The procedure looks like this: ```sql CREATE PROCEDURE GetEmployeeData @DepartmentId INT AS BEGIN SELECT EmployeeID, FirstName, LastName FROM Employees WHERE DepartmentID = @DepartmentId; END ``` In another part of my code, I am trying to execute this stored procedure and insert its results into a temporary table: ```sql CREATE TABLE #TempEmployees ( EmployeeID INT, FirstName NVARCHAR(50), LastName NVARCHAR(50) ); INSERT INTO #TempEmployees EXEC GetEmployeeData @DepartmentId = 5; ``` The inserted records are expected, but I keep hitting that behavior, which I suspect is due to no records being returned from the stored procedure for the given `DepartmentId`. However, when I run the stored procedure directly, I can see the records returned as expected. I've also checked the value of `@DepartmentId`, and it’s definitely valid. I tried using a different department ID, but the result is the same. Is there a particular reason why the `INSERT...EXEC` might unexpected result with this behavior even when the procedure returns rows when executed directly? What can I do to troubleshoot this scenario further? Any insights would be appreciated! My development environment is Linux. Any ideas what could be causing this? My development environment is macOS. Any help would be greatly appreciated! For context: I'm using Sql on Windows 11. Thanks, I really appreciate it! For context: I'm using Sql on macOS.