Create Dynamic SP >8000 Chars - 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

SQL Server Development (2000)

Create Dynamic SP >8000 Chars

Author

Topic

lee_h
Starting Member

36 Posts

Posted - 2004-03-12 : 06:31:18

Hi,

I am writing a stored procedure generator, but have hit a slight issue:

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

declare @procText varchar(8000)

set @sql1 = 'create procedure .....'
set @sql2 = 'some procedure text'

set @procText = @sql1 + @sql2

exec sp_executesql @procText
go

this all works fine until len(sql1)+len(sql2) > 8000
exec sp_executesql @sql1+@sql2 isn't valid

anyone got any bright ideas, i'm all out?

Cheers

Lee

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-12 : 06:58:41

If your string is longer than 4,000 character you cannot use sp_executesql. Simple as that. This procedure accepts only unicode. Your best bet in this case is to use EXEC().
However, as always with the use of dynamic sql you might like to read http://www.sommarskog.se/dynamic_sql.html and see if that's the way to go or if there is not something better.

--Frank
http://www.insidesql.de

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-12 : 07:06:11

In your case you can just do
exec (@sql1 + @sql2 + ...)
This takes a text rather than varchar so you can concatenate to your hearts content.

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources