I'm trying to build a dynamic select statement in a stored procedure, but I'm running out of a varibles max length ie 8000 characters after about 2 passes though the loop. I've enclosed a simple example.

DECLARE @statement varchar(8000)
Set @statement = 'SELECT .....INTO ....FROM .....'

WHILE -- 24 to 48 times
BEGIN

-- This select is about 6000 characters in length
SET @statement = @statement + 'LEFT OUTER JOIN ( SELECT ........)'

END

EXECUTE ( @statement )


Any help will be appreciated

James