iT邦幫忙

0

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

期望數據:

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文件知道問題點在這
enter image description here

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.

1 個回答

0
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名稱

暐翰 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);

暐翰 iT邦大師 1 級 ‧ 2019-05-25 22:28:28 檢舉

daimom
謝謝你
我在S.O也有發問How select dynamic string SQL result by OPENROWSET or SomeFunction in SQL Server - Stack Overflow

目前得到結果是,不使用AP方式只能用cursor + sp_executesql來解決(效能差方式).

我要發表回答

立即登入回答