Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Passing parameters in dynamic procedure

Mar 19 2012 1:21AM by Madhivanan   

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

Tags: t-sql, sql_server, pivot, sqlserver, tsql,


Madhivanan
3 · 40% · 12862
4 Readers Liked this
Guru Samy Liked this on 3/19/2012 2:03:00 AM
Profile · Blog
kingkong0924 Liked this on 3/20/2012 8:08:00 PM
Profile
Madhivanan Liked this on 3/22/2012 12:29:00 AM
Profile · Blog · Facebook · Twitter
Alpesh Gorasia Liked this on 3/30/2012 3:11:00 AM
Profile
4
Liked
 
0
Lifesaver
 
0
Refreshed
 
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Passing parameters in dynamic procedure" rated 5 out of 5 by 4 readers
Passing parameters in dynamic procedure , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]