SqlServer如何select 動態string sql結果從OPENROWSET或是某Function

sql sqlserver

暐翰 2019-05-23 10:16:27 ‧ 3331 瀏覽

期望數據:

with cte as (
	select 'select 1 + 2' tSql union all
	select 'select 1 * 2' tSql
)
select somefunction(tsql) as val
from cte

result :

val
3
2

我的嘗試

1. 使用 OPENROWSET :

單獨使用 OPENROWSET 可以從字串轉結果

SELECT * FROM OPENROWSET('SQLNCLI','Server=(local);Trusted_Connection=yes;','select 1+2') --result : 3

但是會得到以下錯誤

Msg 7314, Level 16, State 1, Line 1 The OLE DB provider "SQLNCLI" for linked server "(local)" does not contain the table tSql . The table either does not exist or the current user does not have permissions on that table.

with cte as (
	select 'select 1 + 2' tSql union all
	select 'select 1 * 2' tSql
)
select (SELECT * FROM OPENROWSET('SQLNCLI','Server=(local);Trusted_Connection=yes;',tSql)) as val
from cte

我查看MSDN文件知道問題點在這

2.使用 SP_Executesql:

create function F_SqlToNumeric(@sql nvarchar(max))
returns numeric(20,8) as 
begin
	declare @v numeric(20,8);
	select @sql = N'select @v_out = ('+@sql+')';
	exec sp_executesql @sql,N'@v_out numeric(20,8) output',@v_out = @v output
	return @v
end;

with cte as (
	select 'select 1 + 2' tSql union all
	select 'select 1 * 2' tSql
)
select F_SqlToNumeric(tSql) as val
from cte

得到以下錯誤 :

error 557 , Only functions and extended stored procedures can be executed from within a function.

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

daimom

iT邦新手 2 級 ‧ 2019-05-23 13:05:02

最佳解答

我直接用OPENROWSSET 會有資料欸
就你上面那段

SELECT * FROM OPENROWSET('SQLNCLI','Server=(local);Trusted_Connection=yes;','select 1+2 ')

但我有先開權限就是了

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
------使用完畢後，記得一定要要關閉它，因為這是一個安全隱患,切記執行下面的SQL語句
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

另外，如果你要指定table的話，前面要加上資料庫名稱及完整的table名稱

回應 3
分享
檢舉

暐翰 iT邦大師 1 級 ‧ 2019-05-23 13:21:53 檢舉

daimom
對,大大你說的情況是可以的,但是當帶入字串變數是不行的
如:

declare @sql nvarchar(20) = 'select 1+2 ';
SELECT * FROM OPENROWSET('SQLNCLI','Server=(local);Trusted_Connection=yes;',@sql)

freedr iT邦新手 5 級 ‧ 2019-05-24 17:52:27 檢舉

declare @sql nvarchar(20) = 'select 1+2 ';
declare @fullsql nvarchar(200) ='';
select @fullsql =concat('SELECT * FROM OPENROWSET(''SQLNCLI'',''Server=(local);Trusted_Connection=yes;'','''
,@sql,''')') ;

exec( @fullsql);