Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 varchar(8000) limit - how to concat a few

Author  Topic 

shell_l_d
Starting Member

17 Posts

Posted - 2010-06-02 : 21:30:08
How can the varchar(8000) limit be overcome?
Using SQL 2000 (SQL Query Analyser - Stored Procedure)...

eg: here's what I have...


-- declare variables for the cursor
DECLARE @tLine integer
DECLARE @tCount integer
DECLARE @tWorkFlow varchar(300)
DECLARE @strWorkFlow1 varchar(8000)
DECLARE @strWorkFlow2 varchar(8000)
DECLARE @strWorkFlow3 varchar(8000)

SET @tCount = 0
SET @tLine = 1
SET @strWorkFlow1 = (SELECT '')
SET @strWorkFlow2 = (SELECT '')
SET @strWorkFlow3 = (SELECT '')

-- prepare the string using a cursor
DECLARE tblCursor SCROLL CURSOR FOR
SELECT
RTRIM(chngStatus) + ' by ' + RTRIM(employee) + ' on ' +
CAST( DATEPART(dd, chngDate) as varchar(2) ) +
CAST( DATENAME(mm, chngDate) as varchar(3) ) +
CAST( DATEPART(yyyy, chngDate) as varchar(4) ) + ' ' +
CONVERT(varchar, chngDate, 108 ) as [workFlow]
FROM @tblTicketHistory

OPEN tblCursor
FETCH NEXT FROM tblCursor INTO @tWorkFlow

WHILE @@fetch_status = 0
BEGIN

SET @tCount = @tCount + 1
PRINT '--------------------COUNT---------------------------'
PRINT @tCount
PRINT @tWorkFlow
PRINT LEN( @tWorkFlow )

IF (@tCount = 1)
SET @strWorkFlow1 = RTRIM(@tWorkFlow)
ELSE
BEGIN
IF (@tLine = 1)
BEGIN
PRINT 'line 1 code'
IF (LEN(@tWorkFlow) + LEN(@strWorkFlow1)) <= 8000
SET @strWorkFlow1 = @strWorkFlow1 + ' > ' + RTRIM(@tWorkFlow)
ELSE
SET @tLine = @tLine + 1
END
IF (@tLine = 2)
BEGIN
PRINT 'line 2 code'
IF (LEN(@tWorkFlow) + LEN(@strWorkFlow2)) <= 8000
SET @strWorkFlow2 = @strWorkFlow2 + ' > ' + RTRIM(@tWorkFlow)
ELSE
SET @tLine = @tLine + 1
END
IF (@tLine = 3)
BEGIN
PRINT 'line 3 code'
IF (LEN(@tWorkFlow) + LEN(@strWorkFlow3)) <= 8000
SET @strWorkFlow3 = @strWorkFlow3 + ' > ' + RTRIM(@tWorkFlow)
ELSE
SET @tLine = @tLine + 1
END
END
PRINT 'LINE'
PRINT @tLine

FETCH NEXT FROM tblCursor INTO @tWorkFlow

END

PRINT '--------------------EXEC---------------------------'
-- ERROR: Must declare the variable '@strWorkFlow1'
Exec( 'SELECT @strWorkFlow1 + @strWorkFlow2 + @strWorkFlow3' ) -- doesnt actually return/display anything at all

PRINT '--------------------SELECT---------------------------'
SELECT @strWorkFlow1 as [str1]
SELECT @strWorkFlow2 as [str2]
SELECT @strWorkFlow3 as [str3]
SELECT @strWorkFlow1 + @strWorkFlow2 + @strWorkFlow3 as [all3] -- truncates to 8000
--SELECT CONVERT(text, @strWorkFlow1 + @strWorkFlow2 + @strWorkFlow3) -- truncates to 8000
--SELECT CAST(@strWorkFlow1 + @strWorkFlow2 + @strWorkFlow3 AS TEXT) -- truncates to 8000

PRINT '--------------------PRINT---------------------------'
PRINT LEN( @strWorkFlow1 )
PRINT @strWorkFlow1
PRINT LEN( @strWorkFlow2 )
PRINT @strWorkFlow2
PRINT LEN( @strWorkFlow3 )
PRINT @strWorkFlow3
PRINT ( LEN( @strWorkFlow1 ) + LEN( @strWorkFlow2 ) + LEN( @strWorkFlow3 ) )
PRINT LEN( @strWorkFlow1 + @strWorkFlow2 + @strWorkFlow3 ) -- max 8000
PRINT @strWorkFlow1 + @strWorkFlow2 + @strWorkFlow3 -- truncates to 8000


CLOSE tblCursor
DEALLOCATE tblCursor

Kristen
Test

22859 Posts

Posted - 2010-06-03 : 04:40:32
Looks like you have found an existing thread about this, for reference here's the link:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52274
Go to Top of Page
   

- Advertisement -