My Technical Notes

Tuesday, 3 January 2017

Querying Results from a stored procedure

To query the result of a stored procedure we first need to execute the stored procedure:


DECLARE @RC int
EXECUTE @RC = [MyDatabase].[dbo].[myStoredProc] 

To store the result of this query into a temporary table, we first need to create the temporary table first and thereafter dump the result of the stored procedure into the new temporary table:


-- creat the temporary table:
CREATE TABLE #tmp(
   [id] int,
   name varchar(64)
   -- other cols here
)

-- now call the 
INSERT INTO #tmp
exec [MyDatabase].[dbo].[myStoredProc] 

A problem with the above method is that we need to write the CREATE TABLE and specify all the columns which might be a laborious task.

An alternative to creating a temp table and writing all the column names is to use the OPENROWSET to run the query:


SELECT tmp.*
FROM OPENROWSET(
    'SQLOLEDB', 
    'Server_name';  -- the server name e.g .\SQLEXPRESS
    'username';     -- the username
    'password',     -- the password
    'EXEC [database_name].[dbo].[stored_procedure_name]' -- statement to execute
) AS tmp

For the server name we can use:


SELECT @@SERVERNAME
Unfortunately, the server needs to be configured for this access and you may not have permissions to change the configuration. The following error message was shown for me:


Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online. 

If we had access to the server we can always use OPENQUERY which is easier and more elegant to use than OPENROWSET but at the same time we need to have admin privileges. To do this we first add a linked server called loopback (link):


EXEC master..sp_addlinkedserver 
    @server = 'loopback',  
    @srvproduct = '',
    @provider = 'SQLNCLI',
    @datasrc = @@SERVERNAME;

EXEC master..sp_serveroption 
    @server = 'loopback', 
    @optname = 'DATA ACCESS',
    @optvalue = 'TRUE';

We can then use OPENQUERY to use loopback to query the same server we are on:


-- do a select only
SELECT * FROM OPENQUERY(loopback, 'EXEC [MyDatabase].[dbo].[myStoredProc];');

-- store the results of the stored procedure into a temporary table
SELECT * INTO #tmp FROM OPENQUERY(loopback, 'EXEC [MyDatabase].[dbo].[myStoredProc];');

Lastly we can use C# to run the stored procedure and then we can determine the column names and column types by interrogating the DataTable and then outputting a CREATE TABLE statement (link):



No comments: