• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2310
  • Last Modified:

excedding limit varchar(8000)

Hi,
I am having diffuclty in preparing dynamic select statemet where length exceed 8000 characters because too many columns in tables.

How can i overcome this situation..Is there anyway to retain if exceeds 8000 characters..

thnx,
Dharmesh
0
Dharmeshgandhi
Asked:
Dharmeshgandhi
1 Solution
 
CJ_SCommented:
You can use a text field instead.
0
 
DharmeshgandhiAuthor Commented:
no.
its not valid for  local variables.

thnx,
dharmesh
0
 
CJ_SCommented:
You can however use a text field as a paramter of the stored procedure and use that variable instead.
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
illCommented:
create table ##a (  id int , value varchar(7900) )
declare @text varchar(8000), @text1 varchar(8000), @col varchar(64), @tab varchar(64), @htab varchar(64), @lasttab varchar(64), @i int, @trig varchar(4000), @historyLength varchar(64)

-- just take what is needed from declaration

-- check your string while preparing and insert after every 7900 chars into ##a. id must be ascendant. then:



/****************************  EXEC LONG STRING *************/

set @text='declare @id int,@inText varchar(8000) '
set @i=0
set @count= ( select count(*) from ##a )
while @i< @count begin
      set @text= @text +', @text'+ convert( varchar(10), @i)+ ' varchar(8000)'
      set @i=@i+1
end
set @text= @text+ 'declare tmpcur insensitive cursor for select id, value from ##a order by id open tmpcur'
set @text= @text+ ' fetch next from tmpcur into @id, @inText'
set @text= @text+ ' while @@fetch_status=0 begin '
set @i=0
while @i< @count begin
      set @text= @text+ 'if @id= '+ convert( varchar(10),@i+1)+ ' begin set @text' +convert ( varchar(10), @i)+ '= @inText end '-- print @text' +convert ( varchar(10), @i)+ ' end '
      set @i= @i+1
end
set @text= @text+ ' fetch next from tmpcur into @id, @inText end close tmpcur deallocate tmpcur '
set @text= @text+' exec ('''''
set @i=0
while @i< @count begin
      set @text= @text+ '+  @text'+ convert ( varchar(10), @i)
      set @i= @i+1
end
set @text= @text+')'
exec ( @text)


i hope i  pasted it correctly
0
 
LowfatspreadCommented:
use the
sp_executesql stored procedure to execute your dynamic sql statement...

you need to pass the statement in a set of NVARCHAR(4000)  variables

to the procedure

exec sp_executesql @Nvar1 + @nvar2 + @Nvar3 etc


...

you will eventually meet the hard limit of 64K for an sql statement however...

0
 
MauriceBicheCommented:
This is found to be a interesting one and the one that we are currently facing while converting our database into non-unicode supported datatype (char/varchar/text) into unicode supported datatypes. (nvarchar/nchar/ntext).
This article is quite useful.
0
 
illCommented:
-- static user table & SP code i'm using currently. for nvarchar it has to be modified to smaller sizes. doesn't support multiple 8000+ at once.
create table sLongString (  
      id int ,
      value varchar(8000)
)
GO

create procedure sExecLong
AS
declare @i int, @count int,  @text varchar(8000)
set @text='declare @id int,@inText varchar(8000) '
set @i=0
set @count= ( select count(*) from sLongString )
while @i< @count begin
      set @text= @text +', @text'+ convert( varchar(10), @i)+ ' varchar(8000)'
      set @i=@i+1
end
set @text= @text+ 'declare longTextcur insensitive cursor for select id, value from sLongString order by id open longTextcur '
set @text= @text+ ' fetch next from longTextcur into @id, @inText'
set @text= @text+ ' while @@fetch_status=0 begin '
set @i=0
while @i< @count begin
      set @text= @text+ 'if @id= '+ convert( varchar(10),@i+1)+ ' begin set @text' +convert ( varchar(10), @i)+ '= @inText end '-- print @text' +convert ( varchar(10), @i)+ ' end '
      set @i= @i+1
end
set @text= @text+ ' fetch next from longTextcur into @id, @inText end close longTextcur deallocate longTextcur '
set @text= @text+' exec ('''''
set @i=0
while @i< @count begin
      set @text= @text+ '+  @text'+ convert ( varchar(10), @i)
      set @i= @i+1
end
set @text= @text+')'
exec ( @text)
truncate table sLongString
GO
0
 
JaccoCommented:
Thanx! This solves a problem.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now