A Dynamic Sql Query is some thing that changes according to the user input.
Dynamic Queries can be executed using
Execute(@SqlStmt)
or
exec(@SqlStmt)
or
sp_executesql
Check this below dynamic pivot for example..
Declare @Id varchar(4), @Sql varchar(400),@Cols varchar(40)
Create Table #Temp
(
Order_Id Int,
InventoryId Int,
ItemType Varchar(40),
total Int
)
Insert into #Temp
Values(1,5,'Orange',5000),(1,4,'Apple',3000),(2,1,'Mango',3400),(2,5,'Orange',1700)
Set @Id=2
Set @Cols= Stuff((Select Distinct '],['+ItemType From #Temp
Where Order_Id=@Id for Xml Path(''),Type).value('.','VARCHAR(Max)'), 1, 2,'')+']'
Set @Sql= 'Select '+@Cols + 'From (Select ItemType,total From #Temp
Where Order_Id= '+ @Id +')SourceTable pivot (Sum(total) For ItemType in ('
+ @Cols + '))PivotTable'
Exec(@Sql)
Drop Table #Temp
As the user changes the Id the Item Type picked for the pivot will be changed..
It will come in handy while using TableNames as variables etc...