hans_vd
asked on
max length varchar
Hi, I have a stored procedure that creates and executes SQL insert statements using dynamic SQL. This procedure worked fine until today. The length of the insert statement exceeded 8000 characters and it seems like the varchar datatype cannot hold more than 8000 characters.
Anyone knows an easy workaround?
Can I use another datatype, that allows more than 8000 characters?
I hope I don't have to redesign my programs, just because sometimes the query gets too long.
My database is SQL Server 7.
Anyone knows an easy workaround?
Can I use another datatype, that allows more than 8000 characters?
I hope I don't have to redesign my programs, just because sometimes the query gets too long.
My database is SQL Server 7.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you cannot add another parameter, you can do this:
create procedure proc ( @sqltext text )
as
declare @sqltext1 varchar(4000)
declare @sqltext2 varchar(4000)
declare @sqltext3 varchar(4000)
select @sqltext1 = substring( @sqltext, 1, 4000),
@sqltext2 = substring( @sqltext, 4001, 4000),
@sqltext3 = substring( @sqltext, 8001, 4000)
exec ( @sqltext1 + @sqltext2 + @sqltext3 )
create procedure proc ( @sqltext text )
as
declare @sqltext1 varchar(4000)
declare @sqltext2 varchar(4000)
declare @sqltext3 varchar(4000)
select @sqltext1 = substring( @sqltext, 1, 4000),
@sqltext2 = substring( @sqltext, 4001, 4000),
@sqltext3 = substring( @sqltext, 8001, 4000)
exec ( @sqltext1 + @sqltext2 + @sqltext3 )
No, adathelad's right, you can't use a datatype text as a variable. This example shows how to pull a long script from a text field in a table and execute it.
DECLARE table_script CURSOR
SCROLL STATIC FOR
SELECT table_key, table_name
FROM Northwind.dbo.dd_tables
ORDER BY table_key
OPEN table_script
FETCH FIRST FROM table_script
INTO @key, @name
WHILE @@FETCH_STATUS = 0
BEGIN
-- The script field in the dd_table is a datatype of TEXT
-- This routine allows up to 72000 char to be loaded and executed
SELECT @script1 = ISNULL(substring(script, 0, 8000), ''),
@script2 = ISNULL(substring(script, 8000, 8000), ''),
@script3 = ISNULL(substring(script, 16000, 8000), ''),
@script4 = ISNULL(substring(script, 24000, 8000), ''),
@script5 = ISNULL(substring(script, 32000, 8000), ''),
@script6 = ISNULL(substring(script, 40000, 8000), ''),
@script7 = ISNULL(substring(script, 48000, 8000), ''),
@script8 = ISNULL(substring(script, 56000, 8000), ''),
@script9 = ISNULL(substring(script, 64000, 8000), '')
FROM Northwind.dbo.dd_tables
where table_key = CONVERT(VARCHAR, @key)
EXEC(@script1 + @script2 + @script3 + @script4 + @script5 + @script6 + @script7 + @script8 + @script9)
FETCH NEXT FROM table_script
INTO @key, @name
END
CLOSE table_script
DEALLOCATE table_script
DECLARE table_script CURSOR
SCROLL STATIC FOR
SELECT table_key, table_name
FROM Northwind.dbo.dd_tables
ORDER BY table_key
OPEN table_script
FETCH FIRST FROM table_script
INTO @key, @name
WHILE @@FETCH_STATUS = 0
BEGIN
-- The script field in the dd_table is a datatype of TEXT
-- This routine allows up to 72000 char to be loaded and executed
SELECT @script1 = ISNULL(substring(script, 0, 8000), ''),
@script2 = ISNULL(substring(script, 8000, 8000), ''),
@script3 = ISNULL(substring(script, 16000, 8000), ''),
@script4 = ISNULL(substring(script, 24000, 8000), ''),
@script5 = ISNULL(substring(script, 32000, 8000), ''),
@script6 = ISNULL(substring(script, 40000, 8000), ''),
@script7 = ISNULL(substring(script, 48000, 8000), ''),
@script8 = ISNULL(substring(script, 56000, 8000), ''),
@script9 = ISNULL(substring(script, 64000, 8000), '')
FROM Northwind.dbo.dd_tables
where table_key = CONVERT(VARCHAR, @key)
EXEC(@script1 + @script2 + @script3 + @script4 + @script5 + @script6 + @script7 + @script8 + @script9)
FETCH NEXT FROM table_script
INTO @key, @name
END
CLOSE table_script
DEALLOCATE table_script
ASKER
I did it like Adathelad proposed
It's not atractive though.
ADW