-1

I'm looking for a solution to use openrowset with variables, without an execute, so that I can use it in a table function.

I know that openrowset doesn't accept '+' and that I can't call exec() to create a dynamic call.

I'm looking for something like

SELECT * 
FROM OPENROWSET(
         'SQLOLEDB', 
         'DRIVER={SQL Server};SERVER=(local);Trusted_Connection=yes', 
         @sql
     ) 
  • 3
    Can you give a more detailed description of what you are trying to achieve? How are you planning to use those variables? Are you looking for something similar to sp_executesql? – Sebastian Meine Sep 17 '13 at 12:56
  • I'm looking for something like select * FROM OPENROWSET('SQLOLEDB', 'DRIVER={SQL Server};SERVER=(local);Trusted_Connection=yes', @sql) – GamingNow Sep 17 '13 at 13:24
  • 1
    Is there a reason you're using OPENROWSET instead of a linked server? – Mike Fal Sep 17 '13 at 16:22
2

OPENROWSET does not accept parameters. If you want to use OPENROWSET with parameters, you need to use dynamic T-SQL.

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

Reference : The OPENROWSET Trick, Revisited

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.