Insert rows into table from stored procedure

Since stored procedures are generally the most efficient way to call and retrieve data, it’s natural to want to encapsulate logic in them and call them when needed.

With this use also comes the need to retrieve the data they return. When you already know the datatypes and columns being returned from the procedure, this is a relatively easy task. Let’s start by looking at an example call.

ListName = 'MyList'
,ListNumber = 1

-- this table will house our results
ListName VARCHAR(25),
ListNumber INT

-- finally, execute and insert into our table
EXEC dbo.GetList

That was relatively easy, however dynamically retrieving the result set from a stored procedure without knowing the columns and datatypes returned in advance has historically proven to be a difficult task. Fortunately with the advent of OPENROWSET, our life as developers has become much simpler. Using openrowset is very simple when we do not have procedures that take parameters, However when parameters become involved the game changes, however there is a workaround.

In using OPENROWSET we need to turn on the ability to run distributed queries on your server. To enable this is simple, given you have the appropriate permissions. It’s a simple process when we do not have to pass in parameters.

sp_configure 'Ad Hoc Distributed Queries',1


Using OPENROWSET for a Stored Procedure with No Parameters

To get the result set is simple, simply call OPENROWSET passing the parameter of driver name, connection string and command text and we’re finished.

SELECT * INTO #Jobs FROM OPENROWSET('SQLNCLI', 'server=SERVERNAMEINSTANCENAME;trusted_connection=yes', 'set fmtonly off exec msdb.dbo.sp_help_job')

Using OPENROWSET for a Stored Procedure with Parameters

A whole different scenario presents itself when we need to use the above method to call a stored procedure and pass dynamic parameters. The issue is that you cannot concatenate a string within OPENROWSET. Here is how we do it:

First we need to create a procedure that wraps OPENROWSET. Make sure to replace the server and instance with your servernameinstancename values.

@TempTableName nvarchar(100)
,@SPAndParameters nvarchar(MAX)
DECLARE @Driver nvarchar(20)
DECLARE @ConnectionString nvarchar(200)
DECLARE @SQL nvarchar(MAX)
DECLARE @RowsetSQL nvarchar(MAX)SET @Driver = '''' + 'SQLNCLI' + ''''
SET @ConnectionString = '''' +'server=SERVERNAMEINSTANCENAME;trusted_connection=yes'+ ''''
SET @RowsetSQL = '''' +'SET FMTONLY OFF ' + '' + @SPAndParameters + '' + ''''

SET @SQL = @SQL + @Driver + ',' + @ConnectionString + ',' + @RowsetSQL + ')'

-- Now that the procedure is created, let's run our query
INTO #holdingtable
FROM OPENROWSET('SQLNCLI','server=SERVERNAMEINSTANCENAME;trusted_connection=yes','SET FMTONLY OFF exec sp_who 1')
-- set the parameters of the procedure
SET @SPWithParams = 'exec sp_who ' + @SPID
-- execute the procedure
EXEC ProcToTable 'holdingtable', @SPWithParams
-- view the results
SELECT * FROM #holdingtable

This procedure we created, uses a temp table to insert the result of our parameterized stored procedure query. So with that in mind, we need to first create the temp table prior to executing the procedure. We need to execute the procedure using default parameters and using a SELECT TOP 0 *, in order to create the structure of our table.

The other option is to use OPENQUERY. It is basically the same as OPENROWSET, however you can use this option if for some reason you can’t get OPENROWSET to work. There is an additional setup using this. You have to create a linked server connection to your own local server. Click here for instructions to create a local linked server

-- Enable Ad Hoc Distributed Queries
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
INTO dbo.List
FROM OPENQUERY(me, 'set fmtonly off exec dbo.GetList')

references from : here


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s