T-SQL筆記45_openrowset使用筆記

型1.一段查詢語句

select * from openrowset('SQLOLEDB','SQL服務器';'用戶名';'密碼','SELECT * FROM 資料庫名.dbo.[表名]')

型2.呼叫接獲預存程序的結果(無參數)

select * from openrowset('SQLOLEDB','SQL服務器';'用戶名';'密碼','SET NOCOUNT ON;SET FMTONLY OFF;exec sp名')

型3.呼叫接獲預存程序的結果(有參數)

select * from openrowset('SQLOLEDB','SQL服務器';'用戶名';'密碼','SET NOCOUNT ON;SET FMTONLY OFF;exec sp名 ''參數1'',''參數2'' ')

這類型要注意通常字串類參數原本我們會用兩個單引號來包覆

sp名 '參數1','參數2'

放到openrowset需要double 也就變成4個單引號

sp名 ''參數1'',''參數2''

Ref:

SELECT from openrowset (exec stored procedure)

https://www.sqlservercentral.com/forums/topic/select-from-openrowset-exec-stored-procedure

SELECT with OPENROWSET from a stored procedure with parameter

https://dba.stackexchange.com/questions/195443/select-with-openrowset-from-a-stored-procedure-with-parameter

使用 OPENROWSET 過濾來自 Store Procedure 的資料

https://blog.poychang.net/filter-the-results-of-a-stored-procedure/

openrowset stored procedure passing parameters 

https://social.msdn.microsoft.com/Forums/en-US/da88f299-7ed4-433e-9475-ff1d0f8bc6c6/openrowset-stored-procedure-passing-parameters?forum=transactsql