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...
Submit an article or tip