You cannot fight the limitations of sp_decribe_first_result_set in any way. I have spent a lot of time (and continue spending some each couple of mounts) and the result is we need to learn to live with them.
As to your question, I have also stored procedure which is generating different T-SQL dynamic statements depending on its parameters. And like you, I need to store its result set sometimes (and we need to define the structure of the temporary table before storing the data).
In order to improve this, I've added a mode, which is describing the result set, instead of returning the data. Its something like this:
IF [dbo].[fn_Utils_RegexIsMatch] ('(?i)\[DescribeFirstResultSet]', @MiscSettings) = 1
BEGIN;
SET @DynamicTSQLStatement = [dbo].[fn_Utils_RegexReplace] (@DynamicTSQLStatement, 'CREATE TABLE #([^\s]+)', 'DECLARE @$1 TABLE');
SET @DynamicTSQLStatement = REPLACE(@DynamicTSQLStatement, '#', '@');
SET @DynamicTSQLStatement = [dbo].[fn_Utils_RegexReplace] (@DynamicTSQLStatement, 'DROP TABLE IF EXISTS.+?;', '');
SELECT [column_ordinal]
,REPLACE([name], '@', '#') AS [name]
,[system_type_name]
,IIF([column_ordinal] = 1, '', ',') + '[' + REPLACE([name], '@', '#') + '] ' + UPPER([system_type_name]) AS [column_definition]
FROM [sys].[dm_exec_describe_first_result_set] (@DynamicTSQLStatement, NULL, 0);
EXEC [sys].[sp_describe_first_result_set] @DynamicTSQLStatement;
RETURN;
END;
ELSE
BEGIN;
EXEC sp_executesql @DynamicTSQLStatement;
END;
So, in my case, the developer is setting the columns ze needs in a parameter but is calling the stored procedure with the description option. So, the result is like this:
Few notes:
- you can try to use the definition in order to create a temporary table and then in dynamic T-SQL statement to execute your stored procedure
- note, that I am replacing/converting my temporary tables to table variables (this is needed due limitations again)
- if you want to use CLR
regex replace function check this answer
