What to use instead of varchar(8000) in SQL Server 2000

Hi,

I am trying to write a stored procedure and in that I am using exec function, but the query length is greater than varchar(8000) so what should I use instead so that I can include character sql string of a greater length than varchar(8000) ?

Attached alongwith is code snippet.

Please give me a solution to this.

Regards,
Pranjal
select @sql1 = 'insert into #temp8' +
	' SELECT CLT_SITE,CLT_CD,COUNT(BILL_STATUS) AS TOTAL, ''B1'' as BILL_STATUS' +
	' FROM IMG_INFO WHERE (IMG_DT < LEFT(CONVERT(VARCHAR(20),GETDATE()-1),11)' + 
	' AND CRT_DATE = ''1/1/1900'' AND IMG_DT >= ''10/01/2008'' AND BILL_STATUS = ''E'')' +
	' GROUP BY CLT_SITE, CLT_CD' 
 
exec @sql1

Open in new window

pranjal_dsAssistant ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Blackninja2007Commented:
did sql 2000 have the TEXT or NTEXT type which take over 2,000,000,000 characters ?

0
Anthony PerkinsCommented:
Certainly. Unfortunately you could not declare local variables of type text or ntext so the author is SOL.
0
hamishdCommented:
If you are using MS SQL 2005 or above, use datatype varchar(max)

http://teratrax.com/articles/varchar_max.html
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Anthony PerkinsCommented:
>>If you are using MS SQL 2005 or above<<
The title of this question is a dead giveaway. :)
0
pranjal_dsAssistant ManagerAuthor Commented:
Hi,
I am using SQL Server 2000 only so please provide me a solution to varchar(8000) or any other parallel solution so that I can execute the above mentioned code.

Regards,
Pranjal
0
Anthony PerkinsCommented:
The best you can do is break it down into multiple varchar(8000) variables and then concatenate them when you do Exec as in:

Exec @SQL1 + @SQL2 + @SQL3 + ... + @SQLN
0
pranjal_dsAssistant ManagerAuthor Commented:
I am getting error "Incorrect syntax near +" when using the above suggestion.
0
pranjal_dsAssistant ManagerAuthor Commented:
I have found a solution to this by using a cursor with union all for multiple queries output combination.

Regards,
Pranjal
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Software

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.