anthony.green (8/17/2012)
Change the function to return varchar(max) and have a str of varchar(max)
The function returns a maximum of 4757 characters (for a leap year). It's inefficient but works ok.
Try measuring the stringlengths:
declare @cols VARCHAR(8000)
set @cols = dbo.[getAllDaysInYear]()
SELECT LEN(@cols) -- should be 4757
DECLARE @query VARCHAR(max)
SET @query = N'SELECT userid, '+@cols +'
FROM
( select userId,CONVERT(VARCHAR(10), loggedDate, 120) as loggedDate,logCount from loginStats
)
AS Q1
PIVOT
(
sum( logCount) FOR loggedDate IN ('+@cols +')
) AS Q2'
SELECT LEN(@query) -- should be 4757 * 2 plus 50 or so
Also, try running the code with a subset of dates, like this:
declare @cols VARCHAR(8000)
set @cols = '[2012-01-07],[2012-01-08],[2012-01-09],[2012-01-10],[2012-01-11],[2012-01-12],[2012-01-13],[2012-01-14],[2012-01-15],[2012-01-16]'
DECLARE @query VARCHAR(max)
SET @query = N'SELECT userid, '+@cols +'
FROM
( select userId,CONVERT(VARCHAR(10), loggedDate, 120) as loggedDate,logCount from loginStats
)
AS Q1
PIVOT
(
sum( logCount) FOR loggedDate IN ('+@cols +')
) AS Q2'
print @query
EXECUTE(@query)
This from BOL, regarding PRINT:
A message string can be up to 8,000 characters long if it is a non-Unicode string, and 4,000 characters long if it is a Unicode string. Longer strings are truncated. The varchar(max) and nvarchar(max) data types are truncated to data types that are no larger than varchar(8000) and nvarchar(4000).
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden