Process stored proc data from within another in stored proc
Say you have one stored procedure, that returns a very wide table; i.e. lots of columns – or even worse, a variable number of columns and you want another stored procedure to process that data in some way before returning it.
you could create a temporary table, with all those columns, and insert the data into it. – an approach that won’t even work if you have a variable number of columns.
If you’re using SQL server 2012, then you can use dm_exec_describe_first_result_set_for_object to help with this, but say, you’re using SQL server 2008 …
This is where OPENROWSET comes in, where you can make SQL server call itself, as if it were a remote database.
EXEC sp_configure ‘Show Advanced Options’, 1
RECONFIGURE
EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1
RECONFIGURE
declare @CityName varchar(200)
set @CityName = ‘Derry’
declare @SQL varchar(1000)
set @SQL = ‘SELECT distinct something FROM OPENROWSET(”SQLNCLI”, ”Server=(local);Trusted_Connection=yes;”,
”yourdb.dbo.sp_SearchResultsByCityName ””’ + @CityName + ”””’)
order by something’
print @SQL
exec(@SQL)
There’s about two levels of indirection in there, but it works … after an hour of trial and error!
ps: If you’re using temp tables in your stored procedure, you also have to use
SET FMTONLY OFF
SET NOCOUNT ON
