March 23, 2016 at 4:41 pm
I see, got it! Thanks for all the info and your time! 🙂
March 24, 2016 at 6:41 am
Thanks for the article.
March 24, 2016 at 3:36 pm
The stored procedure presented in this article appears to be incomplete as of the time I am writing this post. The last line I can see in the textbox is " FOR XML PATH(N''), TYPE).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 2, N'');". Can a moderator take a look at the article and confirm all the information is present.
This is a great solution and I am am very much looking forward to adding it to my toolbox. Thank you, Orlando, for this excellent contribution.
March 24, 2016 at 9:26 pm
william.palace (3/24/2016)
The stored procedure presented in this article appears to be incomplete as of the time I am writing this post. The last line I can see in the textbox is " FOR XML PATH(N''), TYPE).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 2, N'');". Can a moderator take a look at the article and confirm all the information is present.
There was a formatting issue with the code where the last few lines of the procedure definition ended up outside the formatted code block and were showing as plain-text but it has been corrected.
This is a great solution and I am am very much looking forward to adding it to my toolbox. Thank you, Orlando, for this excellent contribution.
Thanks for the positive feedback. I am quite pleased you find the technique useful.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 22, 2017 at 3:28 am
I am using this same technique in a general SP rendering the output of an SP as HTML.
This cannot be done with OPENQUERY.
September 22, 2017 at 5:31 am
h.tobisch, When sp_makewebtask was removed from the platform it left a gap that it sounds like you may have worked to fill to some degree.
There are some folks that would be interested to see the final product. Could you post your code here? I think the topic would make for a great article on this site too.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 22, 2017 at 10:45 am
September 22, 2017 at 12:45 pm
Great script, thanks!
In some of my (ahem) less disciplined stored procedures the returned result set contains columns with no name (as a consequence of evaluating an expression and not naming the result). These silently drop out of the column list assembled by FOR XML PATH - I wonder if coalescing in a 'default' column name would be of benefit?
Something like: QUOTENAME(COALESCE([name], 'unnamed_column_' + CAST(column_ordinal AS VARCHAR(5))))
Of course, if your SP actually returns a column called 'unnamed_column_1' then this is worse 🙂
September 22, 2017 at 5:36 pm
Xedni - Friday, September 22, 2017 10:45 AMNice post. Now if someone could just figure out how to do this with procs that return multiple result sets...
If all the resultsets have the same shape (i.e. same number of columns with the same data type in each ordinal position) the technique in the article will succeed and will capture all data in one temp table where the columns names come from the first resultset. However, you'd have no way to know which resultset each row in the temp table came from. Demo below.
To move the data into separate temp tables, or to be able to know which resultset each row came out of, you can accomplish this using SQLCLR. Be careful using SQLCLR for this purpose because while functionally it gives you more than native T-SQL in this instance it will consume a lot memory when using it on procedures that return very large resultsets.
Demo:CREATE OR ALTER PROC dbo.a
AS
BEGIN
SELECT 1 AS a;
SELECT 2 AS b;
--SELECT 1 AS a, 2 AS b;
END
go
-- if temp table exists from a previous run, drop it
IF OBJECT_ID(N'tempdb..#result') IS NOT NULL
DROP TABLE #result;
-- create the basis for a temp table that will store the results of our stored procedure.
-- more columns will be added to the table later. we create the table declaratively
-- so it will be in scope when we look to populate it using INSERT..EXEC.
CREATE TABLE #result
(
-- this column will not interfere with the INSERT...EXEC below, i.e. SQL Server
-- will correctly skip attempting to map a column in the proc
-- resultset to the IDENTITY column
result_id INT IDENTITY(1, 1)
NOT NULL
);
-- before, empty table with only our identity column
SELECT *
FROM #result;
-- dynamically add columns to our temp table that can capture the data in the resultset
-- of the stored procedure we will later call using INSERT...EXEC
EXEC dbo.alter_table_for_first_result_set_from_object
@object_name = N'dbo.a',
@table_name = N'#result';
-- before, empty temp table with all our columns
SELECT *
FROM #result;
-- capture the results
INSERT INTO #result
EXEC dbo.a;
-- after, with data from our stored procedure
SELECT *
FROM #result;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 22, 2017 at 5:37 pm
Xedni - Friday, September 22, 2017 10:45 AMNice post.
Almost forgot! Thanks for the kind feedback.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 22, 2017 at 5:40 pm
Richard Swinbank - Friday, September 22, 2017 12:45 PMGreat script, thanks!In some of my (ahem) less disciplined stored procedures the returned result set contains columns with no name (as a consequence of evaluating an expression and not naming the result). These silently drop out of the column list assembled by FOR XML PATH - I wonder if coalescing in a 'default' column name would be of benefit?
Something like: QUOTENAME(COALESCE([name], 'unnamed_column_' + CAST(column_ordinal AS VARCHAR(5))))
Of course, if your SP actually returns a column called 'unnamed_column_1' then this is worse 🙂
Thanks for the nice words.
Interesting twist in events! I certainly never thought of that. I think it would be a nice addition. SQL Server accommodates us, so why shouldn't this utility?!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 25, 2017 at 10:08 am
Hi,
wouldnt it be nicer to do this without the "for xml path" stuff -
SELECT @column_list = @column_list + QUOTENAME([name]) + ' ' + [system_type_name] + ', '
FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID(@ObjectName), 0)
or is there any particular reason to do use "for xml path"
Anyway your procedure was a nice inspiration (besides the gotchas with sp_who2 and some other which I filter out using error_type before the table create statement
Well done sir!
BR
Gerald
September 25, 2017 at 7:54 pm
gerald72 - Monday, September 25, 2017 10:08 AMHi,
wouldnt it be nicer to do this without the "for xml path" stuff -
SELECT @column_list = @column_list + QUOTENAME([name]) + ' ' + [system_type_name] + ', '
FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID(@ObjectName), 0)or is there any particular reason to do use "for xml path"
Anyway your procedure was a nice inspiration (besides the gotchas with sp_who2 and some other which I filter out using error_type before the table create statement
Well done sir!
BR
Gerald
Thank you for the feedback, Gerald. I used to employ aggregate concatenation (Microsoft's term, not mine) quite frequently, and then Erland pointed me to some interesting information previously on this site. Unfortunately the MSDN article referenced in the thread has since been retired but I think the thread itself gets the point across:
https://www.sqlservercentral.com/Forums/Dialogs/PostDetails.aspx?PostID=1485702
Happy to hear your thoughts on it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply