Dynamic SQL in a pivot table

Question

I was watching a video on pivot tables and got to thinking, why can't we just push in a sub query into the IN clause, instead of declaring the expected columns statically? Well, turns out you cant haha, but I wasn't satisfied with that so after some research found that Dynamic SQL will allow you to do something similar. Now, I'm not expert on Dynamic SQL by far, but for the most part I understand what is going on. I am, however, having an issue with a specific query. Here is the query:

DECLARE @SQLString AS NVARCHAR(MAX)
 , @custID AS NVARCHAR(MAX) ;
SELECT @Custid = ISNULL(@custid + ',','') + QUOTENAME(custid)
FROM (SELECT Distinct custid FROM Sales.Orders) AS custid
SET @SQLString = N'
With ptable AS
(
SELECT empid, custid , orderid
FROM sales.Orders
)
SELECT empid, ' + @custID + N'
FROM ptable
PIVOT (COUNT(orderid) For custid IN(' + @custID + N')) AS pivtable
ORDER BY empid'
EXEC @SQLString;

The idea here is to have all of the distinct customer ID's found in the order's table as a separate column to track all employee-customer orders. (This is all for practice and in the name of figuring out how SQL works). This table happens to have 91 unique customers. Here's the problem, it works for the first @CustID in the SELECT statement, however for the second @custid in the IN clause, it only returns the first 16 and a missing ] after the 16. Here is the outputted error message:

Msg 203, Level 16, State 2, Line 16 The name ' With ptable AS ( SELECT empid, custid , orderid FROM sales.Orders ) SELECT empid, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52],[53],[54],[55],[56],[58],[59],[60],[61],[62],[63],[64],[65],[66],[67],[68],[69],[70],[71],[72],[73],[74],[75],[76],[77],[78],[79],[80],[81],[82],[83],[84],[85],[86],[87],[88],[89],[90],[91] FROM ptable PIVOT (COUNT(orderid) For custid IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16' is not a valid identifier.

I am struggling to figure out why it worked for the first select clause then failed on the second one. I know this is a ridiculous example, but I guess my curiosity got the better of me and I just want to know. Any help is appreciated!

Answer 1

You are saying:

EXEC @SQLString;

This is like saying:

EXEC N'SELECT 1;';

Which is probably something that looks like it will work on first glance, but go ahead and try it, it fails. You want:

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'SELECT ...;';
EXEC sys.sp_executesql @sql;

I wrote a pretty detailed article on dynamic pivot here:

• https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

But as for dynamic SQL, please see:

• https://sqlblog.org/2011/09/17/bad-habits-to-kick-using-exec-instead-of-sp_executesql

• https://www.mssqltips.com/sqlservertip/3637/protecting-yourself-from-sql-injection-in-sql-server--part-1/

• https://www.mssqltips.com/sqlservertip/3638/protecting-yourself-from-sql-injection-in-sql-server--part-2/

• user2921015: Thanks! This helps a lot, and that worked. I will definitely check out the links, and appreciate you taking the time to post them.