Overcome varchar(8000) - SQL Server Forums

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)

Overcome varchar(8000)

Author

Topic

jwe
Starting Member

13 Posts

Posted - 2007-06-27 : 09:50:16

Hi,

I am trying to overcome the varchar(8000) limit by splitting the results into multiple variables but I am not sure I am doing it correctly.
Any pointers would be appreciated.

DECLARE
@SQL1 VARCHAR (8000),
@SQL2 VARCHAR (8000),
@SQL3 VARCHAR (8000)

SELECT @SQL3 = CASE WHEN LEN(@SQL1) > 7000 THEN @SQL2 ELSE @SQL3 END,
@SQL2 = CASE WHEN LEN(@SQL1) > 7000 THEN @SQL1 ELSE @SQL2 END,
@SQL1 = CASE WHEN LEN(@SQL1) > 7000 THEN '' ELSE @SQL1 END,

@SQL1 = @SQL1
+ ', ' + Char(13) + Char(10) + ' Max(Case ColumnName WHEN ''[' +
c.name + '] '' THEN LastValue ELSE '''' END) AS [' + c.name +']'

from sysobjects as t
join syscolumns as c
on t.id = c.id
join systypes as ty
on ty.usertype = c.usertype
join systypes st
on ty.type= st.type
where t.name = 'Event'
AND c.name NOT IN ('created', 'modified','RowVersion')
AND c.iscomputed = 0
AND st.name IN ('tinyint', 'smallint', 'int', 'money', 'smallmoney',
'decimal', 'bigint', 'datetime',
'smalldateteime', 'numeric', 'varchar', 'nvarchar', 'char', 'nchar',
'bit')
order by c.colid

PRINT (@SQL3 + ' ' + @SQL2 + ' ' + @SQL1)

Kristen
Test

22859 Posts

Posted - 2007-06-27 : 10:26:53

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Execute+dynamic+SQL+that+is+longer+than+varchar,8000

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-27 : 10:28:46

Search for 8000 in this topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-27 : 10:29:57

But can you explain what actually you are trying to do?

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-27 : 10:49:44

I think this is the sort of thing you are trying to do

declare @s1 varchar(8000), @s2 varchar(8000), @s3 varchar(8000)
select @s3 = '', @s2 = '', @s1 = ''

select @s3 = @s3 + case when len(@s1) > 7000 and len(@s2) > 7000 then name + ',' else '' end ,
@s2 = @s2 + case when len(@s1) > 7000 and len(@s2) <= 7000 then name + ',' else '' end ,
@s1 = @s1 + case when len(@s1) <= 7000 then name + ',' else '' end
from sysobjects

select @s1
select @s2
select @s3

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jwe
Starting Member

13 Posts

Posted - 2007-06-27 : 10:57:37

Hi,

In this case I am trying to build up a view by selecting every column (c.name) from
a particular table.
This table has 78 columns so when building the string I go over the varchar 8000 limit
ie.

----------------------------
DECLARE
@SQL VARCHAR (8000)

SET @SQL = 'CREATE VIEW ' + 'DBO' + '.' + 'Event' + '_Deleted' + Char(13) + Char(10)
+ 'AS ' + Char(13) + Char(10) + 'SELECT Col.*, CM.Created, CM.Modified, DD.Deleted FROM '
+ ' (SELECT PrimaryKey' + Char(13) + Char(10)

-- for each column
SELECT @SQL = @SQL
+ ', ' + Char(13) + Char(10) + ' Max(Case ColumnName WHEN ''[' + c.name + '] '' THEN LastValue ELSE '''' END) AS [' + c.name +']'

from sysobjects as t
join syscolumns as c
on t.id = c.id
join systypes as ty
on ty.usertype = c.usertype
join systypes st
on ty.type= st.type
where t.name = 'Event'
AND c.name NOT IN ('created', 'modified','RowVersion')
AND c.iscomputed = 0
AND st.name IN ('tinyint', 'smallint', 'int', 'money', 'smallmoney',
'decimal', 'bigint', 'datetime',
'smalldateteime', 'numeric', 'varchar', 'nvarchar', 'char', 'nchar',
'bit')
order by c.colid

PRINT (@SQL)

---------------------------

Gives me :

CREATE VIEW DBO.Event_Deleted
AS
SELECT Col.*, CM.Created, CM.Modified, DD.Deleted FROM (SELECT PrimaryKey
,
Max(Case ColumnName WHEN '[EventID] ' THEN LastValue ELSE '' END) AS [EventID],
Max(Case ColumnName WHEN '[EntryDate] ' THEN LastValue ELSE '' END) AS [EntryDate],
Max(Case ColumnName WHEN '[NotifiedDate] ' THEN LastValue ELSE '' END) AS [NotifiedDate],
Max(Case ColumnName WHEN '[ArrivedDate] ' THEN LastValue ELSE '' END) AS [ArrivedDate],
Max(Case ColumnName WHEN '[StartedDate] ' THEN LastValue ELSE '' END) AS [StartedDate],
Max(Case ColumnName WHEN '[CompletedDate] ' THEN LastValue ELSE '' END) AS [CompletedDate],
Max(Case ColumnName WHEN '[ServiceOKDate] ' THEN LastValue ELSE '' END) AS [ServiceOKDate],
Max(Case ColumnName WHEN '[ReportedBy] ' THEN LastValue ELSE '' END) AS [ReportedBy],
Max(Case ColumnName WHEN '[ReportedBy] ' THEN LastValue ELSE '' END) AS [ReportedBy],
Max(Case ColumnName WHEN '[ReportedByID] ' THEN LastValue ELSE '' END) AS [ReportedByID],
Max(Case ColumnName WHEN '[ContractID] ' THEN LastValue ELSE '' END) AS [ContractID],
Max(Case ColumnName WHEN '[WorkTypeID] ' THEN LastValue ELSE '' END) AS [WorkTypeID],
Max(Case ColumnName WHEN '[WorkCode] ' THEN LastValue ELSE '' END) AS [WorkCode],
Max(Case ColumnName WHEN '[WorkCode] ' THEN LastValue ELSE '' END) AS [WorkCode],
Max(Case ColumnName WHEN '[ModelORServiceID] ' THEN LastValue ELSE '' END) AS [ModelORServiceID],
Max(Case ColumnName WHEN '[GeographyID] ' THEN LastValue ELSE '' END) AS [GeographyID],
Max(Case ColumnName WHEN '[InventoryID] ' THEN LastValue ELSE '' END) AS [InventoryID],
Max(Case ColumnName WHEN '[CoverCodeID] ' THEN LastValue ELSE '' END) AS [CoverCodeID],
Max(Case ColumnName WHEN '[AgentID] ' THEN LastValue ELSE '' END) AS [AgentID],
Max(Case ColumnName WHEN '[ContactPhone] ' THEN LastValue ELSE '' END) AS [ContactPhone],
Max(Case ColumnName WHEN '[ContactPhone] ' THEN LastValue ELSE '' END) AS [ContactPhone],
Max(Case ColumnName WHEN '[Extension] ' THEN LastValue ELSE '' END) AS [Extension],
Max(Case ColumnName WHEN '[Extension] ' THEN LastValue ELSE '' END) AS [Extension],
Max(Case ColumnName WHEN '[PriorityID] ' THEN LastValue ELSE '' END) AS [PriorityID],
Max(Case ColumnName WHEN '[SitePhone] ' THEN LastValue ELSE '' END) AS [SitePhone],
Max(Case ColumnName WHEN '[SitePhone] ' THEN LastValue ELSE '' END) AS [SitePhone],
Max(Case ColumnName WHEN '[RepeatBack] ' THEN LastValue ELSE '' END) AS [RepeatBack],
Max(Case ColumnName WHEN '[RepeatForward] ' THEN LastValue ELSE '' END) AS [RepeatForward],
Max(Case ColumnName WHEN '[EventStatus] ' THEN LastValue ELSE '' END) AS [EventStatus],
Max(Case ColumnName WHEN '[EventStatus] ' THEN LastValue ELSE '' END) AS [EventStatus],
Max(Case ColumnName WHEN '[Regulation] ' THEN LastValue ELSE '' END) AS [Regulation],
Max(Case ColumnName WHEN '[Regulation] ' THEN LastValue ELSE '' END) AS [Regulation],
Max(Case ColumnName WHEN '[Reference] ' THEN LastValue ELSE '' END) AS [Reference],
Max(Case ColumnName WHEN '[Reference] ' THEN LastValue ELSE '' END) AS [Reference],
Max(Case ColumnName WHEN '[UserId] ' THEN LastValue ELSE '' END) AS [UserId],
Max(Case ColumnName WHEN '[UserId] ' THEN LastValue ELSE '' END) AS [UserId],
Max(Case ColumnName WHEN '[OnBehalfID] ' THEN LastValue ELSE '' END) AS [OnBehalfID],
Max(Case ColumnName WHEN '[ClientSatisfactionID] ' THEN LastValue ELSE '' END) AS [ClientSatisfactionID],
Max(Case ColumnName WHEN '[RepReference] ' THEN LastValue ELSE '' END) AS [RepReference],
Max(Case ColumnName WHEN '[RepReference] ' THEN LastValue ELSE '' END) AS [RepReference],
Max(Case ColumnName WHEN '[CostAllocationID] ' THEN LastValue ELSE '' END) AS [CostAllocationID],
Max(Case ColumnName WHEN '[Remarks] ' THEN LastValue ELSE '' END) AS [Remarks],
Max(Case ColumnName WHEN '[Remarks] ' THEN LastValue ELSE '' END) AS [Remarks],
Max(Case ColumnNam

So I need to put the results into more than one variable to get the full resluts but I am unsure on how to do this.

Many Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-27 : 11:10:28

<<
In this case I am trying to build up a view by selecting every column (c.name) from
a particular table.
>>

Why dont you just type all columns and create a View?
That would reduce your time much more than spending time to find out how to use more than one variable to build sql dynamically. Make the things easier. Read this link to know why, when and where Dynamic SQL should be used
www.sommarskog.se/dynamic_sql.html

If you think typing all columns would be difficult, do

Select column_name+',' from information_schema.columns
where table_name='yourtable'

and build your query accordingly

Also read about Cross-tab reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

jwe
Starting Member

13 Posts

Posted - 2007-06-27 : 11:29:12

Thanks,

Sorry I should have explained myslef a bit better.
I am going to build a generic SP with this so I can just pass in the table name to build the view hence the reason I am doing it this way.

I tried following the advice from nr and in the link but I think I am missing something.

--------------

declare
@sql1 varchar(8000),
@sql2 varchar(8000),
@sql3 varchar(8000)

select @sql3 = '', @sql2 = '', @sql1 = ''

-- for each column

SELECT

@sql3 = @sql3 + case when len(@sql1) > 7000 and len(@sql2) > 7000
then ' Max(Case ColumnName WHEN ''[' + c.name + '] '' THEN LastValue ELSE '''' END) AS [' + c.name +']' + Char(13) + Char(10)
else '' end,

@sql2 = @sql2 + case when len(@sql1) > 7000 and len(@sql2) <= 7000
then ' Max(Case ColumnName WHEN ''[' + c.name + '] '' THEN LastValue ELSE '''' END) AS [' + c.name +']' + Char(13) + Char(10)
else '' end ,

@sql1 = @sql1 + case when len(@sql1) <= 7000
then ' Max(Case ColumnName WHEN ''[' + c.name + '] '' THEN LastValue ELSE '''' END) AS [' + c.name +']' + Char(13) + Char(10)
else '' end

from sysobjects as t
join syscolumns as c
on t.id = c.id
join systypes as ty
on ty.usertype = c.usertype
join systypes st
on ty.type= st.type
where t.name = 'Event'
AND c.name NOT IN ('created', 'modified','RowVersion')
AND c.iscomputed = 0
AND st.name IN ('tinyint', 'smallint', 'int', 'money', 'smallmoney',
'decimal', 'bigint', 'datetime',
'smalldateteime', 'numeric', 'varchar', 'nvarchar', 'char', 'nchar',
'bit')
order by c.colid

PRINT (@sql1+@sql2+@sql3)

----------------------------

I get a bit more results but I think I am missing something.

Many Thanks,

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-27 : 11:37:47

Better to create a resultset from the sp than to create a string.
When I do this I build the rows up in a temp table.
Using that temp table you can build a string and execute it if you wish (the solution I gave earlier would work for that).
I usually bcp the result of the SP out to a file and execute the file using osql if I want to do it automatically.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jwe
Starting Member

13 Posts

Posted - 2007-06-27 : 12:16:01

Thanks, I will try that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources