Varchar(8000) Is Not Enough... What Can I Do?

Hi all!

I'm using this store proc to get a pivot table in SQL:


USE [m0851System]
GO
/****** Object: StoredProcedure [dbo].[sp_CrossTabIntoTable] Script Date: 01/25/2008 09:59:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_CrossTabIntoTable]
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
-- AJOUTÉ PAR JULIEN BONNIER 17 juillet 2007
,@tbl_result varchar(100),
@fld_sufx varchar(10)
-- FIN JULIEN BONNIER
AS

--DECLARE @sql varchar(8000), @delim varchar(1)
DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

-- AJOUTÉ PAR JULIEN BONNIER 17 juillet 2007
-- MODIFIÉ PAR JULIEN BONNIER 25 janvier 2008 (ajout de la clause case)
IF LEFT(@tbl_result,1)='#'
BEGIN
IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type='U' AND name='' + @tbl_result + '')
EXEC ('DROP TABLE ' + @tbl_result + '')
END
ELSE
BEGIN
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name='' + @tbl_result + '')
EXEC ('DROP TABLE ' + @tbl_result + '')
END
-- FIN JULIEN BONNIER

EXEC ('SELECT ' + @pivot + ' AS pvt INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')

SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pvt'

-- MODIFIÉ PAR JULIEN BONNIER 18 juillet 2007
--SELECT @sql=@sql + '''' + convert(varchar(100), pvt) + ''' = ' +
SELECT @sql=@sql + '''' + convert(varchar(100), pvt) + '' + @fld_sufx + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pvt) + @delim + ' THEN ' ) + ', ' FROM ##pivot
-- FIN JULIEN BONNIER
-- AJOUTÉ PAR JULIEN BONNIER 15 octobre 2007
ORDER BY pvt
-- FIN JULIEN BONNIER

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
PRINT(LEN(@sql))
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
-- AJOUTÉ PAR JULIEN BONNIER 17 juillet 2007
SELECT @select=stuff(@select, charindex(' FROM ', @select), 6, ' INTO ' + @tbl_result + ' FROM ')
-- FIN JULIEN BONNIER

--EXEC (@select)
PRINT('YOYO'+@select)
RETURN
SET ANSI_WARNINGS ON


But now my @sql and @select that are varchar(8000) get bigger than 8000 for one of my reports... So my query fails ever times.

What can I do to fix it ?

Thanks in advance.

Or Tho