Dynamic Cross-Tab Query too long?

I am using the Dynamic Cross-Tab code supplied in an article from SQL
Server Magazine (http://www.winnetmag.com/SQLServer/...608/15608.html).
I modified the script to generate a temp table inside the stored
procedure, and then use this temp table as the source for the
cross-tab. However, the problem seems to be that the dynamic SQL
string generated by the script is longer than what can be stored in
the @.SQL variable. The Cross-tab works great, so long as the amount of
data to be pivoted is small.

Is there any way around this? E.g. a User defined type, or another
data type which can store more characters?

Thanks,

Tim

CREATE procedure CBN_CrossTab

@.StudyID varchar(100), --Model ID passed from web app - Only one model
can be selected
@.Level int --The level to which the taxonomy should be rolled up

As

DECLARE

@.Table as sysname, --Table to crosstab
@.OnRows as nvarchar(128), --Groupuing key values (on rows)
@.OnRowsAlias as sysname, --Alias for grouping cloumn
@.OnCols as nvarchar(128), --destination columns (on columns)
@.SumCol as sysname, --data cels
@.SQL AS varchar(8000), -- String to hold generated SQL String
@.NEWLINE as char(1) --Holds the New Line Character for the code

SET @.OnRowsAlias = Null
SET @.SumCol = Null
SET @.NEWLINE = CHAR(10)

-- Generate the Temp table for the taxa and counts
CREATE TABLE #RefOrganisms (sampleid int, txtTaxa varchar(75),
fltCount float)

INSERT INTO #RefOrganisms(sampleid, txtTaxa, fltCount)

SELECT dbo.tblsampledata.sampleid,
dbo.CBN_RecursTaxa(dbo.tblbenthic.organism_tsn, @.Level, " ") AS Taxa,
SUM(dbo.tblbenthic.[count] /
dbo.tblsitedetail.numberofreps) AS SumCount
FROM dbo.tblstudylist INNER JOIN
dbo.tblsite ON dbo.tblstudylist.studyid =
dbo.tblsite.study_id INNER JOIN
dbo.tblsitedetail ON dbo.tblsite.siteid =
dbo.tblsitedetail.site_id INNER JOIN
dbo.tblsampledata ON
dbo.tblsitedetail.sitedetailsid = dbo.tblsampledata.sitedetails_id
INNER JOIN
dbo.tblbenthic ON dbo.tblsampledata.sampleid =
dbo.tblbenthic.sample_id INNER JOIN
dbo.iter_intlist_to_table(@.StudyID) i ON
dbo.tblstudylist.studyid = i.number INNER JOIN
dbo.tblbenthictaxa ON dbo.tblbenthic.organism_tsn =
dbo.tblbenthictaxa.tsn
WHERE (dbo.tblsampledata.qaqc = 0) AND (dbo.tblsampledata.status =
2) AND (dbo.tblbenthictaxa.rank_id >= @.Level)
GROUP BY
dbo.tblsampledata.sampleid,
dbo.CBN_RecursTaxa(dbo.tblbenthic.organism_tsn, @.Level, " ")

-- Identify the Temp table info for the CrossTab

SELECT @.Table = '#RefOrganisms'
SELECT @.OnRows = 'sampleid'
SELECT @.OnCols = 'txtTaxa'
SELECT @.OnRowsAlias = Null
SELECT @.SumCol = 'fltCount'

--STEP1 BEGININNING OF SQL STRING
SET @.sql = 'SELECT'+ @.newline +' '+ @.onrows +
CASE
WHEN @.ONROWSALIAS IS NOT NULL THEN ' AS ' + @.ONROWSALIAS
ELSE ''
END

CREATE TABLE #KEYS(KEYVALUE NVARCHAR(100)NOT NULL PRIMARY KEY)

DECLARE @.KEYSSQL AS VARCHAR (1000)

SET @.KEYSSQL = 'INSERT INTO #KEYS ' + 'SELECT DISTINCT CAST(' +
@.ONCOLS + '
AS NVARCHAR(100)) ' + 'FROM ' + @.TABLE
EXEC (@.KEYSSQL)

DECLARE @.KEY AS NVARCHAR(100)

SELECT @.KEY = MIN(KEYVALUE) FROM #KEYS

WHILE @.KEY IS NOT NULL
BEGIN
SET @.SQL = @.SQL + ' ,'+ @.NEWLINE +
' SUM(CASE CAST(' + @.ONCOLS +
' AS NVARCHAR(100))' + @.NEWLINE +
' WHEN N''' + @.KEY +
''' THEN '+ CASE
WHEN @.SUMCOL IS NULL THEN '1'
ELSE @.SUMCOL
END + @.NEWLINE +
' ELSE 0' + @.NEWLINE +
' END) AS [' + @.KEY + ']'
SELECT @.KEY = MIN(KEYVALUE) FROM #KEYS
WHERE KEYVALUE > @.KEY
END

SET @.SQL = @.SQL + @.NEWLINE +
'FROM ' + @.TABLE + @.NEWLINE +
'GROUP BY ' + @.ONROWS + @.NEWLINE +
'ORDER BY ' + @.ONROWS

PRINT @.SQL --+ @.NEWLINE --FOR DEBUG
EXEC (@.SQL)
GO"Tim Pascoe" <tim.pascoe@.cciw.ca> wrote in message
news:19555f2b.0402240832.1e138923@.posting.google.c om...
> I am using the Dynamic Cross-Tab code supplied in an article from SQL
> Server Magazine
(http://www.winnetmag.com/SQLServer/...608/15608.html).
> I modified the script to generate a temp table inside the stored
> procedure, and then use this temp table as the source for the
> cross-tab. However, the problem seems to be that the dynamic SQL
> string generated by the script is longer than what can be stored in
> the @.SQL variable. The Cross-tab works great, so long as the amount of
> data to be pivoted is small.
> Is there any way around this? E.g. a User defined type, or another
> data type which can store more characters?
> Thanks,
> Tim

<snip
One possibility is to use several variables, then execute them like this:

EXEC(@.sql1 + @.sql2 + @.sql3 + ...)

See EXECUTE in Books Online for more information. There are data types which
hold more than 8000 characters (text, ntext), but they cannot be used with
EXEC().

Simon