Database Programming: The OPENROWSET Trick, Revisited

One of the most popular posts in the history of this little corner of the Internets is one from August, 2005, which describes a method for accessing stored procedure output in a SELECT statement which I’ve come to refer to as “the OPENROWSET trick.”

On the occasion of this blog’s 750th post(!), I thought it would be fun to return to the blog’s roots and present a long post full of T-SQL. This analysis was brought on by a not-so-innocent question on this technique from Giles Collingwood:

Nice - does it work with parameters too?

At this point, my answer is.. kind of.

I’ve revisited this code in light of Giles’ question. The current state of my research may be found at the bottom of this post. I’ve included new syntax to discover the local server/instance name and build it into the string, rather than relying on the “(local)” syntax of OPENROWSET. I’ve also included two different approaches to filtering the results of stored procedure output.

Referencing the code block.. once we declare our variables, we then build our instance name. This will include the machine name and, if applicable, the instance name:

SET @Server = CAST(SERVERPROPERTY('MachineName') AS nvarchar(128)) +
                CASE CAST(SERVERPROPERTY('InstanceName') AS nvarchar(128))
                    WHEN NULL THEN ''
                    ELSE N'\' + CAST(SERVERPROPERTY('InstanceName') AS nvarchar(128))
                END

We then use this value to build the first OPENROWSET call, define the parameters for it, and call it for spids 1 and 7, as well as the spid running the query:

-- Example 1: parameterized call to sp_who using sp_executesql
SET @SQLString = N'
SELECT *
FROM OPENROWSET (''SQLOLEDB'',''Server=' + @Server + ';TRUSTED_CONNECTION=YES;'',''set fmtonly off exec master.dbo.sp_who'')
AS tbl
WHERE spid = @filter'

SET @Parms = N'@filter int'

-- Example 1a: return results for spid = 1
SET @int = 1
EXEC sp_executesql @SQLString, @Parms, @filter = @int

-- Example 1b: return results for spid = 7
SET @int = 7
EXEC sp_executesql @SQLString, @Parms, @filter = @int

-- Example 1c: return results for current spid
SET @int = @@spid
EXEC sp_executesql @SQLString, @Parms, @filter = @int

Note that this approach will allow re-use of a cached query plan. But, as the comment in the SQL stream notes..

-- this is all well and good,
-- but we didn't pass a parameter to the stored procedures,
-- we just filtered the results

To this point, I’ve succeeded in using dynamic SQL to build a parameterized call:

-- Example 2a: return results for spid = 1
SET @string = '1'
SET @SQLString = N'
SELECT *
FROM OPENROWSET (''SQLOLEDB'',''Server=' + @Server + ';TRUSTED_CONNECTION=YES;'',''set fmtonly off exec master.dbo.sp_who ' + @string + ''')
AS tbl'
EXEC sp_executesql @SQLString
EXEC (@SQLString)

Note that you can make this call via either sp_executesql or EXEC(). No query plan re-use here. 

I’ve not yet succeeded in getting sp_executesql’s parameters inside the OPRENROWSET call, where I’d have the best of all world’s – parameterized calls to stored procedures with fully cacheable query plans.

I’ll keep playing with this as my workload permits. Thanks, Giles, for a great question!

-wp

Code Block

SET NOCOUNT ON
DECLARE @SQLString nvarchar(max),
        @Server nvarchar(max),
        @Parms nvarchar(500),
        @filter int,
        @int int,
        @string nvarchar(500)

-- build servername, with instancename as appropriate
SET @Server = CAST(SERVERPROPERTY('MachineName') AS nvarchar(128)) +
                CASE CAST(SERVERPROPERTY('InstanceName') AS nvarchar(128))
                    WHEN NULL THEN ''
                    ELSE N'\' + CAST(SERVERPROPERTY('InstanceName') AS nvarchar(128))
                END

-- Example 1: parameterized call to sp_who using sp_executesql
SET @SQLString = N'
SELECT *
FROM OPENROWSET (''SQLOLEDB'',''Server=' + @Server + ';TRUSTED_CONNECTION=YES;'',''set fmtonly off exec master.dbo.sp_who'')
AS tbl
WHERE spid = @filter'

SET @Parms = N'@filter int'

-- Example 1a: return results for spid = 1
SET @int = 1
EXEC sp_executesql @SQLString, @Parms, @filter = @int

-- Example 1b: return results for spid = 7
SET @int = 7
EXEC sp_executesql @SQLString, @Parms, @filter = @int

-- Example 1c: return results for current spid
SET @int = @@spid
EXEC sp_executesql @SQLString, @Parms, @filter = @int

-- this is all well and good,
-- but we didn't pass a parameter to the stored procedures,
-- we just filtered the results

-- Example 2: parameterized calls to sp_who using sp_executesql and exec

-- Example 2a: return results for spid = 1
SET @string = '1'
SET @SQLString = N'
SELECT *
FROM OPENROWSET (''SQLOLEDB'',''Server=' + @Server + ';TRUSTED_CONNECTION=YES;'',''set fmtonly off exec master.dbo.sp_who ' + @string + ''')
AS tbl'
EXEC sp_executesql @SQLString
EXEC (@SQLString)

-- Example 2b: return results for current spid
SET @string = CAST(@@spid as nvarchar(5))
SET @SQLString = N'
SELECT *
FROM OPENROWSET (''SQLOLEDB'',''Server=' + @Server + ';TRUSTED_CONNECTION=YES;'',''set fmtonly off exec master.dbo.sp_who ' + @string + ''')
AS tbl'
EXEC sp_executesql @SQLString
EXEC (@SQLString)
GO


this copyrighted material was originally posted at https://blogs.technet.com/wardpond

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work. however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.