Home > Uncategorized > Process stored proc data from within another in stored proc

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

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: