Can I use the results of a stored proc in a SELECT statement?

http://www.sommarskog.se/share_data.html


On 5/13/08 1:40 PM, in article
d2b9bf01-d3d9-4c55-a1ed-b6e5ee9f690d...oglegroups.com, "Jonathan
Sion" wrote:

> This is probably a classic question: what is the TSQL syntax to use
> the results of a SP in a statement? something like:
>
> select * from sp_spaceused
>
> (which doesnt work)
>
> or any other proc?
>
> thanks
> Jonathan

You can use OPENROWSET:

SELECT T.*
FROM OPENROWSET('SQLNCLI',
'Server=MyServer;Trusted_Connection=yes;',
'EXEC sp_spaceused') AS T;

Or OPENQUERY:

SELECT * FROM OPENQUERY(Loopback, 'EXEC sp_spaceused');

Read Erland Sommarskog's article on some issues with this approach:
http://www.sommarskog.se/share_data.html#OPENQUERY

HTH,

Plamen Ratchev
http://www.SQLStudio.com

> SELECT * FROM OPENQUERY(Loopback, 'EXEC sp_spaceused');

Well, you need to create a linked server called "Loopback" first... :-)

Yes, this is why posted the link to Erland's article, explains all details.

Or perhaps should file suggestion on Connect to have it built-in for future
versions... :)

Plamen Ratchev
http://www.SQLStudio.com

Right, I only brought it up because in the first sample you used 'MyServer'
but in the second it looks like "Loopback" is a magic word of some kind.




"Plamen Ratchev" wrote in message
news04791B0-287E-46D2-A5EF-E3DEA6C30D5F@microsoft.com...
> Yes, this is why posted the link to Erland's article, explains all
> details.
>
> Or perhaps should file suggestion on Connect to have it built-in for
> future versions... :)
>
> Plamen Ratchev
> http://www.SQLStudio.com

Thanks very much, everybody. so I guess there is no simple syntax, but
it can be done. I tried the above sample and it works, for example:
SELECT T.*
FROM
OPENROWSET('SQLNCLI','Server=PY1DBMDMD01\QA;Trusted_Connection=yes;',
'EXEC sp_spaceused ''dbo.dim_MERCH_LOCATION''') AS T;

works good. HOWEVER, when I add a database to the connection string:

SELECT T.*
FROM
OPENROWSET('SQLNCLI','Server=PY1DBMDMD01\QA;Database=Merch_DMT;Trusted_Connection=yes;',
'EXEC sp_spaceused ''dbo.dim_MERCH_LOCATION''') AS T;

I get an error:

The OLE DB provider "SQLNCLI" for linked server "(null)" supplied
inconsistent metadata for a column. The name was changed at execution
time.

why is that? how can I change the database in the connection string?
thanks again!

Did you try

EXEC Merch_DMT..sp_spaceused ...

?



"Jonathan Sion" wrote in message
news:b09f5080-592b-4b4f-afac-adbfff2ef267@d77g2000hsb.googlegroups.com...
> Thanks very much, everybody. so I guess there is no simple syntax, but
> it can be done. I tried the above sample and it works, for example:
> SELECT T.*
> FROM
> OPENROWSET('SQLNCLI','Server=PY1DBMDMD01\QA;Trusted_Connection=yes;',
> 'EXEC sp_spaceused ''dbo.dim_MERCH_LOCATION''') AS T;
>
> works good. HOWEVER, when I add a database to the connection string:
>
> SELECT T.*
> FROM
> OPENROWSET('SQLNCLI','Server=PY1DBMDMD01\QA;Database=Merch_DMT;Trusted_Connection=yes;',
> 'EXEC sp_spaceused ''dbo.dim_MERCH_LOCATION''') AS T;
>
> I get an error:
>
> The OLE DB provider "SQLNCLI" for linked server "(null)" supplied
> inconsistent metadata for a column. The name was changed at execution
> time.
>
> why is that? how can I change the database in the connection string?
> thanks again!

Jonathan Sion (yoni@nobhillsoft.com) writes:
> Thanks very much, everybody. so I guess there is no simple syntax, but
> it can be done. I tried the above sample and it works, for example:
> SELECT T.*
> FROM
> OPENROWSET('SQLNCLI','Server=PY1DBMDMD01\QA;Trusted_Connection=yes;',
> 'EXEC sp_spaceused ''dbo.dim_MERCH_LOCATION''') AS T;
>
> works good. HOWEVER, when I add a database to the connection string:
>
> SELECT T.*
> FROM
> OPENROWSET('SQLNCLI','Server=PY1DBMDMD01\QA;Database=Merch_DMT;Trusted_Connection=yes;',
> 'EXEC sp_spaceused ''dbo.dim_MERCH_LOCATION''') AS T;
>
> I get an error:
>
> The OLE DB provider "SQLNCLI" for linked server "(null)" supplied
> inconsistent metadata for a column. The name was changed at execution
> time.
>
> why is that? how can I change the database in the connection string?
> thanks again!

sp_spaceused returns two result sets, so it is very difficult to do
a SELECT from it anyway. I guess the multiple result sets causes some
problems here.

Using OPENQUERY/OPENROWSET is not an easy path to take. Since the
result sets from sp_spaceused are on row each, I guess you real case
is different. In my article, I discuss a couple of methods which I
think are better than OPENQUERY.

http://www.sommarskog.se/share_data.html

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

If you really want an understanding of this issue beyond what's in bol check
out:
'Anatomy of sql server part I - what is a stored procedure'
http://beyondsql.blogspot.com/2007/1...er-part-i.html


"Jonathan Sion" wrote in message
news:d2b9bf01-d3d9-4c55-a1ed-b6e5ee9f690d@f63g2000hsf.googlegroups.com...
> This is probably a classic question: what is the TSQL syntax to use
> the results of a SP in a statement?