sql - 如何在sql-server中执行多个过程脚本

Question

以下是我用 SQL SERVER 编写的选择查询

select 'exec pr_tblmaster_split ''tblmaster_'+  Cast(year_id as nvarchar(4000)) +'''' as qry
from  tblmaster
group by year_id 
order by year_id asc

这将返回

qry
-------------------------------------
exec pr_tblmaster_split 'tblmaster_1'
exec pr_tblmaster_split 'tblmaster_2'

所以我的问题是如何使用函数/过程运行返回查询？

---

我正在尝试将 PostgreSQL 转换为 SQL Server，所以

在 PostgreSQL 中，我可以像这样使用 FOR 循环

declare 
rw record;
begin
  for rw in 
         select 'select fn_tblmaster_spliting(''tblmaster_'||t.tbl||''');' crTables from (
         select distinct(year_id) tbl from tblmaster order by acyrid asc)t
   loop
         execute rw.crTables;
   end loop;
   end

Answer 1

将结果存储在变量中，并通过sp_executesql执行。试试这个。

declare @sql nvarchar(max)=''
select @sql+= 'exec pr_tblmaster_split ''tblmaster_'+  Cast(year as nvarchar(4000)) +'''; ' 
from  tblmaster
group by year_id 
order by year_id asc

exec sp_executesql @sql