VISUAL STUDIO FEEDS

In this post Dynamic PIVOT in SQL Server 2005, I have shown a way to dynamically generate the column values with names. I used to get mails frequently from people on how to pass parameters when executing the procedure. The following will find total sales made by each employee for each year(from Employees and Orders table from Northwind databases) EXEC dynamic_pivot 'SELECT e.lastname, o.OrderDate FROM northwind..Employees as e INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID) ', 'Year(OrderDate)', 'Count(OrderDate)' One user mailed me that the following did not work DECLARE @year int SET @year=1984 EXEC dynamic_pivot 'SELECT e.lastname, o.OrderDate FROM northwind..Employees as e INNER JOIN northwind..Orders as o ON

(e.EmployeeID=o.EmployeeID) WHERE YEAR(o.OrderDate)=@year', 'Year(OrderDate)', 'Count(OrderDate)' In the above, @year will not be replaced with it's value. The correct method is to assign it to a variable and use like below DECLARE @year int, @sql varchar(max) SET @year=1984 SET @sql='SELECT e.lastname, o.OrderDate FROM northwind..Employees as e INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID) WHERE YEAR(o.OrderDate)='+cast(@year as varchar(4)) EXEC dynamic_pivot @sql, 'Year(OrderDate)', 'Count(OrderDate)' So you need to aware of this when passing parameters to dynamic sql...(Read whole news on source site)