Select column1,column2 from(exec SP_name) Where column1 = xx
-
Select column1,column2 from(exec SP_name) Where column1 = xx
> Its possible???
-
Not that I have found. You could load a temporary table then query the temporary table.
-
As far as I know Lynn's solution is the only one. You can create a table that has the same column types as the exec result, and then insert into this the result of the exec. The table can be a temp table.
INSERT #mytablewithsamecolumntypesasSP_name EXEC SP_name
You can read more what you can do with the exec statement on:
http://msdn2.microsoft.com/en-us/library/Aa175921(SQL.80).aspx
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
Not entirely true...
SELECT
a.*
FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;', 'EXEC sp_who') AS a
where a.spid = 10
N 56°04'39.16"
E 12°55'05.25" -
Cool, one way to use a stored proc as a table. I would think you would need to be careful doing this, though, by making sure the stored procedure didn't return multiple result sets.
-
Does this have to be a stored proc? If you use a function then you could query it -
CREATE
FUNCTION fn_foo()
RETURNS TABLE
AS
RETURN
(
select * from sometable
)
GO
select
* from fn_foo()
-
The caveat is that only the first resultset is processed
SELECT
a.*
FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;', 'exec sp_spaceused') AS a
N 56°04'39.16"
E 12°55'05.25" -
Wow, I really like it
, Thanks Peter.One small note about it: it seems to require 'Ad Hoc Distributed Queries' to be set, which is off by default. Some organizational security policies may not allow this.
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply