Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
     ) 
share|improve this question

closed as unclear what you're asking by Paul White, dezso, Max Vernon, Nick Chammas, StanleyJohns Sep 19 '13 at 3:58

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

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
add comment

1 Answer 1

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

share|improve this answer
add comment

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