Execute a SQL with more than 8000 in length

Question

Execute a SQL with more than 8000 in length

Dinesh Asanka

SSChampion

Points: 11058
More actions
May 17, 2005 at 12:59 am

#93007

I need to execute a dynamic SQL which will have more than 10,000 characters in length. As this is a dynamic sql there is no guarantee of the length of the SQL. I know that there is no way that I can define text variable inside the Sp and varchar field can't have more than 8000 in length.
So what can I Do?
What are your practices for these types of problems?

My Blog: http://dineshasanka.spaces.live.com/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sara karasik SSCommitted Points: 1573 More actions · Answer 1

Can you break this into two stored procedures, one which is a compiled, stored procedure, which calls your dynamic sp?

If what you are passing is a very long string of variables, you might try to store the variables in a table, populating the table before running the sproc, and truncating it after the sproc runs.

Does this help any? Your question does not provide many details.

Best of luck,

Sara

fhanlon SSChampion Points: 13131 More actions · Answer 2

Sara has a great point. If what are are doing involves parameters which are going to be part of a where clause and may or may not exist check out http://www.sqlservercentral.com/scripts/contributions/96.asp which works greate and helps avoid dynamic sql.

Francis

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

You should be able to do it with 2 @Variables. Here is a very long proof, because I wanted to make sure 8000 characters were captured in both @Variables.

CREATE TABLE #TEST( TextField varchar(8000))

INSERT INTO #TEST VALUES( 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')

DECLARE @SQL1 varchar(8000),

@SQL2 varchar(8000)

SELECT @SQL1 = 'SELECT * FROM #TEST;SELECT * FROM #TEST;SELECT * FROM #TEST;SELECT * FROM #TEST;SELECT * FROM #TEST;' +

'SELECT * FROM #TEST;SELECT * FROM #TEST;SELECT * FROM #TEST;SELECT * FROM #TEST;SELECT * FROM #TEST;' +

'SELECT * FROM #TEST;SELECT * FROM #TEST;SELECT * FROM #TEST;SELECT * FROM #TEST;SELECT * FROM #TEST;'

SELECT @SQL2 = 'SELECT * FROM #TEST;SELECT * FROM #TEST;SELECT * FROM #TEST;SELECT * FROM #TEST;SELECT * FROM #TEST;' +

'SELECT * FROM #TEST;SELECT * FROM #TEST;SELECT * FROM #TEST;SELECT * FROM #TEST;SELECT * FROM #TEST;' +

'SELECT * FROM #TEST;SELECT * FROM #TEST;SELECT * FROM #TEST;SELECT * FROM #TEST;SELECT * FROM #TEST;'

EXEC( @SQL1 + @SQL2)

DROP TABLE #TEST

I wasn't born stupid - I had to study.