Adding a where clause to a Pivot Query

#11

On Dec 2, 12:12*pm, Plamen Ratchev <Pla... (AT) SQLStudio (DOT) com> wrote:

Quote:

The best would be if you are on SQL Server 2008 to use a table-valued parameter to send the selected dates as table to
the procedure and join or use the IN predicate to filter only those dates..

If you are on prior version of SQL Server then you can see Erland's article on how to handle list format and split it to
table format:http://www.sommarskog.se/arrays-in-sql-2005.html

Since you already use dynamic SQL for the pivot, you can also send the dates as string, like "'20090626', '20090731'"
and then concatenate the parameter to the dynamic SQL:

...
FROM DiaryPctWeek
WHERE weekdat IN (' & @weekdat & ') ...

You have to be aware that allowing user entry into dynamic SQL can exposeyour application to SQL injection. Erland's
article explains the issues with this method in more details.

--
Plamen Ratchevhttp://www.SQLStudio.com

Thanks for this info. I am using 2005 so I will take a look at
Erland's article.

I might just put more code in the code behind page in .net to pull it
out there. This is a great start thanks again for your help!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Adding a where clause to a Pivot Query

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: Adding a where clause to a Pivot Query - 12-02-2009 , 12:11 PM