Passing Multiple and Integer Values to Dynamic Query in SQL

In this Post I am going to explain how to filter data in dynamic query in this particular case dynamic pivoting using int variables.

Here is the stored procedure with variables as filters for dynamic query.

Create PROCEDURE skhpReportVillagewisemonthly
    @Fiscalyear int=null,
    @level int =null
AS
BEGIN
Declare @Columns nvarchar(max)
Declare @SQL nvarchar(max)
select @Columns=
COALESCE(@Columns+', ','')+QUOTENAME (VillageID)
from (select VillageID from skhpvillage
) as M
Order By M.VillageID

Set @SQL='
with pivotdata as
(
select VillageID,
Monthid,ActualUnit
from skhpviewDemandBillDetail
where levelid=@level
)

select Monthid,'+@Columns+' from PivotData
Pivot
(
SUM(ActualUnit) for VillageID in (' + @Columns + ')
) as pivotresult
'
exec sp_ExecuteSQL @SQL, N'@level int,@Fiscalyear int', @level,@Fiscalyear
END
GO


In the above example
  @Fiscalyear int=null,
    @level int =null 
are the variable for the data filter in Dynamic Query.

Here is the code where Filter is used using the variable.
Set @SQL='
with pivotdata as
(
select VillageID,
Monthid,ActualUnit
from skhpviewDemandBillDetail
where levelid=@level
)


To Pass the variable to the dynamic query we use exec sp_ExecuteSQL  where we can define variables and send the values as below.
exec sp_ExecuteSQL @SQL, N'@level int,@Fiscalyear int', @level,@Fiscalyear

Keep reading my blog for more such queries and feel free to leave comments. 
Gautam Kumar Gupta