Skip to content

stored procedure

Doing a select on a stored procedure

I came across a scenario today where I just wished that I could do a SQL select on a stored procedure. Akin to something like this …

select * from (exec myStoredProcedure) where column = 'value'

Alas, this is not possible … urg! However, I did manage to find a workaround that gives me basically what I wanted. It involves turning on some insecure settings in SQL sever. So we want to make sure we turn them back off after the fact.

The gist of the below SQL is as follows. We first enable the insecure settings. Next we execute the stored procedure using openrowset and store the results into a temp table. Once we have the data in a temp table we can work with it just like any other table, yeay!! Finally, we want to make sure to turn off the openrowset feature again.

/**********************************/
/* Allow openrowset               */
/**********************************/
sp_configure 'Show Advanced Options', 1
go
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries', 1
go
reconfigure
go
/***********************************/

/***********************************/
/* This is the query               */
/***********************************/
select * into #tempTable from openrowset('SQLNCLI', 'Server=(local);Trusted_Connection=yes;','exec myStoredProcedure')
select * from #tempTable -- <-- Do your stuff here
/***********************************/


/**********************************/
/* Set the insecure settings back */
/**********************************/
sp_configure 'Ad Hoc Distributed Queries', 0
go
reconfigure
go
sp_configure 'Show Advanced Options', 0
go
reconfigure
go
/**********************************/