How can create a table while Execute dynamic @Query

Question

I have a dynamic query in my sp which has different columns related to the inputs that user will enter. After that, In sp I want to join the result of it to the remaining part of sp. So I should have the table for join and as I mentioned I can not have static fields So my question is how can I drop table and create it any time run query instead of creating static table and each time truncate it and insert into it which is fixed. for clearing my question I copy my sp's text maybe help!

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

score 0 · Answer 1 · answered Oct 08 '21 at 05:41

I myself find the answer and it work correctly. I just add the code bellow to my code I make the creation of table also dynamic query and run it sp_executesql. I just want to help anybody who has the same issue as I do.

 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

How can create a table while Execute dynamic @Query

1 Answers1