I have a stored procedure that contains some dynamic SQL. I am trying to execute the stored procedure using
OPENROWSET but am getting the error:
Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'EXEC sp_executesql @SQL, N'@row_count_out INT OUTPUT', @row_count_out = @row_count_table OUTPUT;' in procedure 'nachoTest' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.
My real stored procedure is a bit more complex but this is a working example of the error:
The stored procedure:
CREATE PROCEDURE nachoTest @table_name NVARCHAR(MAX) AS BEGIN SET NOCOUNT ON; DECLARE @row_count_table INT DECLARE @SQL NVARCHAR(MAX) SET @SQL = N'SELECT @row_count_out = COUNT(*) FROM ' + @table_name EXEC sp_executesql @SQL, N'@row_count_out INT OUTPUT', @row_count_out = @row_count_table OUTPUT; SELECT @row_count_table AS row_count, @table_name AS table_name END GO
Calling it with
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'EXEC dbo.nachoTest ''e_MFA_All_Tokens''')
I have read a bit online but cannot figure out how to use
WITH RESULT SETS to fix the code. Ideally I'd like to fix the stored procedure but if I have to change the
OPENROWSET call then that is okay too.