期望數據:

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.