Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #346

Re: large scripted stored procedure

Show all headers | View raw

avcitamer (ttavci@gmail.com) writes:
> In my project, I compile stored procedures dynamically as shown
> below...
> 
> declare @sql01 varchar(max) set @sql01='create procedure dbo.foo() as
> begin bla bla bla......' --8000 bytes long...
> declare @sql02 varchar(max) set @sql02='bla bla bla......bla bla' --
> 8000 bytes long...
> declare @sql03 varchar(max) set @sql03='bla bla bla...... end' -- 8000
> bytes long...
> exec (@sql01+ @sql02+@sql03)
> 
> but sql server sees only first 8000 bytes of script and gives
> error....
> this situation occurs during dynamic creation...(not at query
> analyzer)
> thanks
 
Since varchar(MAX) can fit up to 2GB of data, why mess with many variables?

But apart from that, the scheme as presented should work. Obviously, you are 
doing something wrong, but since I don't see your actual code, I can say 
what it is.

How do you conclude that SQL Server only sees the first 8000 bytes? You are 
not looking at SELECT @sql01 + @sql02 + ... in Mgmt Studio, are you? SSMS 
will only show you the first 8000 characters.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

large scripted stored procedure avcitamer <ttavci@gmail.com> - 2011-05-17 07:30 -0700
  Re: large scripted stored procedure Erland Sommarskog <esquel@sommarskog.se> - 2011-05-17 23:07 +0200
    Re: large scripted stored procedure Gene Wirchenko <genew@ocis.net> - 2011-05-17 15:15 -0700
    Re: large scripted stored procedure avcitamer <ttavci@gmail.com> - 2011-05-17 23:20 -0700
      Re: large scripted stored procedure Erland Sommarskog <esquel@sommarskog.se> - 2011-05-18 07:32 +0000
        Re: large scripted stored procedure avcitamer <ttavci@gmail.com> - 2011-05-18 04:59 -0700

csiph-web