July 5, 2010 at 3:02 am
Hello all,
I want to capture the results of an exec (in this case of sp_spaceused) and add the databasename & current date.
I do this like this:
select @Command = @c_db_s + '.sp_spaceused ''' + @c_s_t + ''', false'
insert into @temp_table_1 exec (@Command)
insert into @temp_table_2 select @c_db , GETDATE(), * from @temp_table
delete @temp_table_1
It seem to work ok but is there a better way to do this? Using a table variable to temporarely store only one row seem a bit like an overkill. But I just don't know how to enrich the result in one command.
Any suggestions?
Thanks
July 5, 2010 at 3:06 am
There are other ways to achieve the same, but they're a bit complicated.
One is using OPENROWSET to call the procedure, but if you find using a table variable to be overkill, this won't help much.
-- Gianluca Sartori
July 5, 2010 at 3:11 am
This is the method I was talking about:
http://blogs.technet.com/b/wardpond/archive/2005/08/01/408502.aspx
I find it a bit tricky and doesn't help code readability.
It's an option, anyway.
-- Gianluca Sartori
July 5, 2010 at 3:27 am
Thank you Gianluca.
This is more or less what I was looking for (had no experience with openrowset yet). Allthough I may not use it here I learned something today!
Thanks again!
July 5, 2010 at 3:42 am
You're welcome.
Another trick could be using a loopback linked server or coding some kind of CLR function. Once again, not so easy, not so readable.
-- Gianluca Sartori
July 5, 2010 at 4:30 am
Hi Ruyven,
Here is an other example on How to SQL Select from Stored Procedure using SQL Server OPENQUERY or OPENROWSET
I liked this method, but I believe it will be better if developers in Microsoft finds a smarter solution to this common problem.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply