stored-procedures - 如何在执行动态@Query 时创建表
问题描述
我的 sp 中有一个动态查询,其中包含与用户将输入的输入相关的不同列。之后,在 sp 中,我想将它的结果加入 sp 的剩余部分。所以我应该有用于连接的表,正如我提到的,我不能有静态字段所以我的问题是如何在运行查询时删除表并创建它,而不是创建静态表,每次截断它并插入其中固定的。为了解决我的问题,我复制了我的 sp 文本可能会有所帮助!
ALTER PROC SP_DamageVasting (@BranchId int , @FromYear int , @ToYear int )
AS
BEGIN
DECLARE @Cols1 AS NVARCHAR(MAX)=''
DECLARE @Cols2 AS NVARCHAR(MAX)=''
DECLARE @Query1 AS NVARCHAR(MAX);
DECLARE @Query2 AS NVARCHAR(MAX);
WHILE (@FromYear <= @ToYear)
BEGIN
SET @Cols1 =@Cols1+'['+CONVERT (nchar(4),@FromYear)+'],'
SET @Cols2 =@Cols2 +'SUM(['+CONVERT (nchar(4),@FromYear)+']) AS ['+CONVERT
(nchar(4),@FromYear)+'],'
SET @FromYear=@FromYear+1
END
SELECT @Query1 ='SELECT PolicyId , BnIssueYear , BranchId , BranchName ,'+ LEFT(@Cols1
,LEN(@Cols1)-1)+
'FROM (SELECT PolicyId , LEFT( Hv.FVsodurDate, 4) AS BnSodurYear ,P.BranchCode AS BranchId,
L.Name AS BranchName ,
LEFT(HV.Date , 4) AS HvYear , SUM(HV.HVAmount) AS HvAmount
FROM DWHdb.amr.V_FactHV AS Hv
INNER JOIN DWHdb.cmn.DimLocationNode AS P (nolock) ON P.LocationId = Hv.FVBNIssueId AND
Hv.FVIssueDate BETWEEN P.Fromdate AND P.ToDate
INNER JOIN DWHdb.cmn.DimLocation AS L ON L.LocationId = P.BranchCode
WHERE Hv.FVIssueDate>''2010-01-01'' AND BranchCode = '+CONVERT (nchar(3),@BranchId)+'
GROUP BY
PolicyId ,
LEFT( Hv.FVIssueDate, 4) ,
P.BranchCode,
L.Name ,
LEFT(HV.Date , 4))AS A
PIVOT (SUM(HvAmount) FOR HvYear IN ('+ LEFT(@Cols1 ,LEN(@Cols1)-1)+')) AS V'
TRUNCATE TABLE [ApiFanavaranService].Bpmn.DamagePerYear --- *****Here I want to have
dynamic table and each time drop and create it *********
INSERT INTO [ApiFanavaranService].Bpmn.DamagePerYear
EXEC (@Query1);
SELECT @Query2='SELECT BNYear ,SUM(Prm) AS Prm,'+LEFT(@Cols2 ,LEN(@Cols2)-1)+'
FROM (
SELECT
BN.PolicyId ,
LN.BranchCode ,
LEFT(BN.FVIssueDate ,4) AS BNYear ,
SUM(BN.Prm ) AS Prm
FROM
DWHdb.amr.FactAmarBn AS BN INNER JOIN
DWHdb.cmn.DimLocationNode AS LN ON BN.FVIssueId = LN.LocationId AND
BN.FVIssueDate>=LN.FromDate AND BN.FVIssueDate<=LN.ToDate
WHERE BN.FVSodurDate >=''2010-01-01'' AND LN.BranchCode ='+CONVERT
(nchar(3),@BranchId)+'
GROUP BY
BN.PolicyId ,
LN.BranchCode,
LEFT(BN.FVIssueDate ,4)) AS A LEFT JOIN
ApiFanavaranService.Bpmn.DamagePerYear AS F ON
A.PolicyId=F.PolicyId AND A.BNYear=F.BnIssueYear
GROUP BY BNYear'
EXEC (@Query2);
END
解决方案
我自己找到了答案并且它工作正常。我只是将下面的代码添加到我的代码中,我创建表也是动态查询并运行它 sp_executesql。我只是想帮助和我有同样问题的人。
DECLARE @Col1 AS NVARCHAR(MAX)=''
DECLARE @Col2 AS NVARCHAR(MAX)=''
DECLARE @Col3 AS NVARCHAR(MAX)=''
DECLARE @Query1 AS NVARCHAR(MAX);
DECLARE @Query2 AS NVARCHAR(MAX);
DECLARE @Query3 AS NVARCHAR(MAX);
WHILE (@FromYear <= @ToYear)
BEGIN
SET @Col1 = @Col1+'['+CONVERT (nchar(4),@FromYear)+'],'
SET @Col2 = @Col2 +'SUM(['+CONVERT (nchar(4),@FromYear)+']) AS ['+CONVERT
(nchar(4),@FromYear)+'],'
SET @Col3 = @Col3 +'['+CONVERT (nchar(4),@FromYear)+'] decimal(38,6),'
SET @FromYear=@FromYear+1
END
DROP TABLE Bpmn.DamagePerYear
SET @Query3 = 'CREATE TABLE [ApiFanavaranService].Bpmn.DamagePerYear (
PolicyId int ,
BnIssueYear int,
BranchId int,
BranchName nchar(200) , '+
LEFT(@Col3 ,LEN(@Col3)-1)+')'
EXEC sp_executesql @Query3
推荐阅读
- python - 在 DynamoDB 中使用查询 IndexName、GSI 而在 python 中不可用 begin_with
- java - 如何从主类外部的方法创建对象并从构造函数打印结果?
- c# - VS 2017 在使用全球化时总是使用默认语言
- c# - 从 ASP.NET Core MVC 中的 SQL 数据库访问用户 ICollection
- javascript - module.exports 更新值
- parallel-processing - 如何停止 mpirun 将多个作业分配给同一个核心而不接触其他核心
- javascript - 给定 id 的禁用元素 - typescript angular
- c++ - 内存泄漏会影响while循环吗?
- python - 如何跳过数据框列中的任何字符串值 - Pandas
- powershell - 无法让 Powershell 分配超过一定大小的数组