Server : openrowset问题

Question

我正在寻找一个不用执行的变量使用openrowset的解决方案，这样我可以在表函数中使用它。

我知道openrowset不接受'+‘，我不能调用exec()来创建动态调用。

我在找类似的东西

SELECT * 
FROM OPENROWSET(
         'SQLOLEDB', 
         'DRIVER={SQL Server};SERVER=(local);Trusted_Connection=yes', 
         @sql
     ) 

Answer 1

开环不接受参数。如果要在参数中使用OPENROWSET，则需要使用动态to。

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

参考资料：OPENROWSET技巧，重获成功