I am trying to create a dynamic SQL statement to create a view.
I have a stored procedure, which based on the parameters passed calls
different stored procedures. Each of this sub stored procedure creates
a string of custom SQL statement and returns this string back to the
main stored procedure.

This SQL statements work fine on there own. The SQL returned from the
sub stored procedure are returned fine. The datatype of the variable
that this sql is stored in Varchar(I have tried using nvarchar also
same problem).

If I have more that 6 SQL statements concated then the main SQL gets
cut off. It doesnt matter in what sequence I create the main SQL.

This is the worst use of SQL I have ever seen in 18 years of
programming in the language. If I put this in my next book, nobody
would believe it was real.

I am trying to create a dynamic SQL statement to create a view. <<
Which completely defeats the purpose of a VIEW and does it by using
the worst possible approach for production code. Dynamic SQL says that
you have no idea what your own schema should look like, so you assume
that any random user sometime in the future is going to do a better
job.
I have a stored procedure, which based on the parameters passed
calls different stored procedures. Each of this sub stored procedure
creates a string of custom SQL statement and returns this string back
to the main stored procedure. <<

Ever have a course in Software Engineering? Obviously not. I cannot
take the time to go over all the probldms with this approach; just get
a book on SE and read it. This has nothing to do with SQL per se, but
with the very **basics** of your trade.

I tried for almost an hour to just read and understand your code. How
do you expect anyone to maintain it? Your inconsistent
capitalization, failure to use alias table names and use of tabs in
the code was also a pain to the reader.
If I have more that 6 SQL statements concated then the main SQL

gets cut off. It doesn't matter in what sequence I create the main
SQL. <<

You are probably generating so much crappy code that you are
overflowing the limits of SQL Server.

Did you know that T-SQL was never meant to be an application
development language? As best I can figure out, this nightmare is a
UNION-ed mess of unrelated reports. Break it apart into VIEWs --
**real** VIEWs that are part of the schema, and not this
conglomeration of confused reports. Get rid of the hard-wired date;
if you want to write a stored procedure, you can make it a parameter.
Get rid of the string month name; in a tiered architecture, display is
done in the front end, not the database (again, that has nothign to do
with SQL per se, but is just a basic progrmaming principle).

This is the worst use of SQL I have ever seen in 18 years of programming in the language. If I put this in my next book, nobody would believe it was real.

I am trying to create a dynamic SQL statement to create a view. << Which completely defeats the purpose of a VIEW and does it by using the worst possible approach for production code. Dynamic SQL says that you have no idea what your own schema should look like, so you assume that any random user sometime in the future is going to do a better job. I have a stored procedure, which based on the parameters passed calls different stored procedures. Each of this sub stored procedure creates a string of custom SQL statement and returns this string back to the main stored procedure. <<

Ever have a course in Software Engineering? Obviously not. I cannot take the time to go over all the probldms with this approach; just get a book on SE and read it. This has nothing to do with SQL per se, but with the very **basics** of your trade.

I tried for almost an hour to just read and understand your code. How do you expect anyone to maintain it? Your inconsistent capitalization, failure to use alias table names and use of tabs in the code was also a pain to the reader. If I have more that 6 SQL statements concated then the main SQL

gets cut off. It doesn't matter in what sequence I create the main SQL. <<

You are probably generating so much crappy code that you are overflowing the limits of SQL Server.

Did you know that T-SQL was never meant to be an application development language? As best I can figure out, this nightmare is a UNION-ed mess of unrelated reports. Break it apart into VIEWs -- **real** VIEWs that are part of the schema, and not this conglomeration of confused reports. Get rid of the hard-wired date; if you want to write a stored procedure, you can make it a parameter. Get rid of the string month name; in a tiered architecture, display is done in the front end, not the database (again, that has nothign to do with SQL per se, but is just a basic progrmaming principle).

If this is how you are writing queries, it is a pretty good bet that the DDL is also a nightmare of bad datatypes, lack of constraints and so forth.

Thank you for your comments. I will get a book on SE and follow some
of your pointer. I understand that you have written a numerous books
on SQL but surely there is not need to be rude! I have overlooked the
alias part but without even knowing what my user requirements are try
not to pass judgement. Again thank you for your feed back considering
that you had to waste 1 hr going through my code. For sure I am going
to buy one of your books, which one would you recommend?

The classics are by Yourdon, DeMacro and Constantine. I also like Gane
& Sarson for systems level stuff -- IST is a better diagramming method
than Yourdon.

I understand that you have written a numerous books
on SQL but surely there is not need to be rude! <<

My wife is the fukatan at a Zen monastary; they beat their students with
sticks :)
For sure I am going to buy one of your books, which one would you

recommend? <<

SQL FOR SMARTIES is the classic that most SQL programmer have on their
desk with post-it notes sticking out of it. It is a collection of SQL
programming techniques. I will start work on the third edition next
month, but I have no idea when it will come out; certainly not until
2005.

my DATA & DATABASES is a good look at foundations and some hueristics
for database in general. Pay attention to scales and measurements and
the design of encoding schemes; I seem to be the only guy who talks
about how to actually design data representations as opposed to
databases.

Terry Halpin's ORM book is great for data modeling.

if you want a mental workout to see if you are getting the idea of
thinking in sets, I also have a SQL PUZZLES & ANSWERS book. Sales were
lousy, but teachers keep using it for homework assignments.

--CELKO--
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I am trying to create a dynamic SQL statement to create a view. I have a stored procedure, which based on the parameters passed calls different stored procedures. Each of this sub stored procedure creates a string of custom SQL statement and returns this string back to the main stored procedure.

This SQL statements work fine on there own. The SQL returned from the sub stored procedure are returned fine. The datatype of the variable that this sql is stored in Varchar(I have tried using nvarchar also same problem).

If I have more that 6 SQL statements concated then the main SQL gets cut off. It doesnt matter in what sequence I create the main SQL.

Supposedly the SQL statement by then exceeds 8000 characters.

The usual remedy is to have more than one SQL variable:

EXEC(@sql1 + @sql2 + @sql3 + ...)

But since you are in a loop, this is not possible. In the next version of
SQL Server, currently in Beta, there is a simple solution: use the new
varchar(MAX) datatype. Here you can fit in 2GB of SQL. Alas, in SQL 2000
you only have the text data type which you cannot assign to.

But there is a solution: insert all the SQL Segments into a temp table: