Execute Big Dynamic SQL in Stored Procedure to Create View

[MD]

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.

Here is the Stored procedure

/**********************************************/
/*Main Stored Procedure */

/**********************************************/
CREATE PROC sp_generate_invoice1 @prev_date NVarchar(1000) ,
@prev_month NVarchar(32)
AS

DECLARE invoice_driver_cur CURSOR FOR
Select driversid From Invoice_drivers

Open invoice_driver_cur
Declare
@C VARCHAR(8000),
@L_args Varchar(8000),
@@sqlstmt Varchar(8000),
@L_driverid Int,
@L_rowcount Int

SET QUOTED_IDENTIFIER ON

SET TEXTSIZE 32768
Set @L_rowcount = 0

-- Drop the previous View
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'custom_invoice')
DROP VIEW custom_invoice

Fetch Next From invoice_driver_cur
Into @L_driverid

-- Create the new View
Set @L_args = N'Create View custom_invoice As'

--Select @L_driverid

WHILE ( @@FETCH_STATUS = 0)
BEGIN
Set @L_rowcount = @L_rowcount + 1
select @L_driverid
If @L_driverid = 2
Begin
Exec sp_invoice_driver2 @prev_date, @prev_month, @@sqlstmt Output

If @L_rowcount > 1
Begin
Set @C = @L_args + ' Union ' + @@sqlstmt
End
Else
Begin
Set @C = @L_args + @@sqlstmt
End
End

If @L_driverid = 3
Begin
Exec sp_invoice_driver3 @prev_date, @prev_month, @@sqlstmt Output

If @L_rowcount > 1
Begin
Set @C = @C + ' Union ' + @@sqlstmt
End
Else
Begin
Set @C = @L_args + @@sqlstmt
End
End

If @L_driverid = 4
Begin
Exec sp_invoice_driver4 @prev_date, @prev_month, @@sqlstmt Output

If @L_rowcount > 1
Begin
Set @C = @C + ' Union ' + @@sqlstmt
End
Else
Begin
Set @C = @L_args + @@sqlstmt
End
End

If @L_driverid = 5
Begin
Exec sp_invoice_driver5 @prev_date, @prev_month, @@sqlstmt Output

If @L_rowcount > 1
Begin
Set @C = @C + ' Union ' + @@sqlstmt
End
Else
Begin
Set @C = @L_args + @@sqlstmt
End
End

If @L_driverid = 6
Begin
Exec sp_invoice_driver6 @prev_date, @prev_month, @@sqlstmt Output

If @L_rowcount > 1
Begin
Set @C = @C + ' Union ' + @@sqlstmt
End
Else
Begin
Set @C = @L_args + @@sqlstmt
End
End

If @L_driverid = 7
Begin
Exec sp_invoice_driver7 @prev_date, @prev_month, @@sqlstmt Output

If @L_rowcount > 1
Begin
Set @C = @C + ' Union ' + @@sqlstmt
End
Else
Begin
Set @C = @L_args + @@sqlstmt
End
End

If @L_driverid = 8
Begin
Exec sp_invoice_driver8 @prev_date, @prev_month, @@sqlstmt Output

If @L_rowcount > 1
Begin
Set @C = @C + ' Union ' + @@sqlstmt
End
Else
Begin
Set @C = @L_args + @@sqlstmt
End
End

If @L_driverid = 10
Begin
Exec sp_invoice_driver_niku @prev_date, @prev_month, @L_driverid,
@@sqlstmt Output

If @L_rowcount > 1
Begin
Set @C = @C + ' Union ' + @@sqlstmt
End
Else
Begin
Set @C = @L_args + @@sqlstmt
End
End
Print @C
Fetch Next From invoice_driver_cur
Into @L_driverid

Continue

End
Close invoice_driver_cur
DeAllocate invoice_driver_cur

Exec (@C)
--EXEC sp_executesql @C
GO
/**********************************************/

/*Sub Procedure sp_invoice_driver2 */

/**********************************************/

CREATE PROC sp_invoice_driver2 @args NVarchar(1000), @prev_month
NVarchar(100),
@sqlstmt Varchar(8000) Output
AS

SET QUOTED_IDENTIFIER ON

SET @sqlstmt = ' Select 1 SortOrder ,
( SELECT Drivers.Description) Description,
(BillingReport.Active_Accounts * Cast(Fee.fee_rate As decimal(4,2)
)) / 12 Amount,
Drivers.Currency
FROM BillingReport, Drivers, Fee
WHERE ( Fee.Driverid = Drivers.Driversid ) and
Drivers.Driversid = 2 and
billingreport.fromdate = ''' + Cast(@args As NVARCHAR(20)) + '''
and
fee.currentmonth = ''' + Cast(@prev_month As NVARCHAR(12)) +' '''

GO

/**********************************************/

This is what the Print Statement give:

/**********************************************/
Create View custom_invoice As Select 1 SortOrder ,
( SELECT Drivers.Description) Description,
(BillingReport.Active_Accounts * Cast(Fee.fee_rate As decimal(4,2)
)) / 12 Amount,
Drivers.Currency
FROM BillingReport, Drivers, Fee
WHERE ( Fee.Driverid = Drivers.Driversid ) and
Drivers.Driversid = 2 and
billingreport.fromdate = '9/1/2004' and
fee.currentmonth = 'September ' Union Select 2,
(SELECT Drivers.Description),
(BillingReport.Zero_Balance * Cast(Fee.fee_rate As decimal(9,2) ))
/ 12 Amount,
Drivers.Currency
FROM BillingReport, Drivers, Fee
WHERE ( Fee.Driverid = Drivers.Driversid ) and
billingreport.fromdate = '9/1/2004' and
fee.currentmonth = 'September' and
Drivers.Driversid = 3 Union Select 3,
(Select Drivers.Description From Drivers Where DriversID = 4),
Count(*) * Cast((select fee.fee_rate
from fee, drivers
where Fee.Driverid = Drivers.Driversid and
fee.currentmonth = 'September' and
Drivers.Driversid = 4 )As decimal(6,2)) / 12,
(Select Drivers.Currency
From Drivers Where DriversID = 4)
From Fund Union Select 4,
(Select Drivers.Description From Drivers Where DriversID = 5),
(((Sum(BillingReport.Man_Reg_Purch + BillingReport.Man_Reg_Red +
BillingReport.Man_Reg_Transexch +
BillingReport.Man_Allo_Purch +
BillingReport.Man_Allo_Red +
BillingReport.Man_Allo_Transexch +
BillingReport.Man_Allo_Adj_Purch +
BillingReport.Man_Allo_Adj_Red +
BillingReport.Man_Allo_Adj_Transexch +
BillingReport.Man_Adj_Purch +
BillingReport.Man_Adj_Red +
BillingReport.Man_Adj_Transexch ) ) +
(Select Sum(Cast(satuscnt As int ))
From Awd_stub
Where CurrentMonth = 'September'))) *
(Cast((select fee.fee_rate
from fee, drivers
where Fee.Driverid = Drivers.Driversid and
fee.currentmonth = 'September' and
Drivers.Driversid = 5 )As decimal(6,2))),
(Select Drivers.Currency From Drivers Where DriversID = 5)
FROM BillingReport
Where billingreport.fromdate = '9/1/2004' Union Select 5,
(Select Drivers.Description From Drivers Where DriversID = 6),
( Sum( BillingReport.Auto_Reg_Purch +
BillingReport.Auto_Reg_Red +
BillingReport.Auto_Reg_TRansexch +
BillingReport.Auto_Allo_Purch+
BillingReport.Auto_Allo_Red +
BillingReport.Auto_Allo_Transexch+
BillingReport.Auto_Allo_Adj_Purch+
BillingReport.Auto_Allo_Adj_Red+
BillingReport.Auto_Allo_Adj_transexch+
BillingReport.Auto_Adj_Purch+
BillingReport.Auto_Adj_Red+
BillingReport.Auto_Adj_Transexch )+
(Select Sum(Cast(Processed_msg As Int))
From XML_messaging
Where CurrentMonth = 'September'))*
(Cast((select fee.fee_rate
from fee, drivers
where Fee.Driverid = Drivers.Driversid and
fee.currentmonth = 'September' and
Drivers.Driversid = 6 )As decimal(6,2))),
(Select Drivers.Currency From Drivers Where DriversID = 6)
FROM BillingReport
Where billingreport.fromdate = '9/1/2004' Union Select 6,
(Select Drivers.Description From Drivers Where DriversID = 7),
( ( a.Accountholder_Active_Accounts -
( select accountholder_active_accounts from billingreport where
Month(fromdate) = Month('9/1/2004')-1 ) )
+
( a.Accountholder_Zero_Balance -
( select accountholder_zero_balance from billingreport where
Month(fromdate) = Month('9/1/2004')-1 ))) *
(Cast((select fee.fee_rate
from fee, drivers
where Fee.Driverid = Drivers.Driversid and
fee.currentmonth = 'September' and
Drivers.Driversid = 7 )As decimal(6,2))),
(Select Drivers.Currency From Drivers Where DriversID = 7)
FROM BillingReport a Where a.fromdate = '9/1/2004' Union Select 7,
(Select Drivers.Description From Drivers Where DriversID = 8),
( Select telephone From cfxbill Where currentmonth = 'September') *
(Cast((select fe

Thanks for any help
MD

[--CELKO--]

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

Try things more like this:

CREATE VIEW Driver_2_invoice (description, from_date, amount,
currency)
AS
SELECT D1.description,
B1.from_date,
(B1.active_accounts * F1.fee_rate)/12.00,
D1.currency
FROM BillingReports AS B1, Drivers AS D1, Fee AS F1
WHERE F1.driver_id = 2
AND D1.driver_id = 2;

But did you notice that "BillingReports" is CROSS JOIN-ed? When you
use the VIEW, you apply a from_date to it to get the range you want.

and so on for the other UNION-ed queries.

CREATE VIEW Driver_3_invoice (description, from_date, amount,
currency)
AS
SELECT D1.description, B1.from_date,
B1.zero_balance * F1.fee_rate) / 12.00,
D1.currency
FROM BillingReport AS B1, Drivers AS D1, Fee AS F1
WHERE F1.driver_id = 3
AND D1.driver_id = 3;

Etc.

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.

[MD]

jc*******@earthlink.net (--CELKO--) wrote in message news:<18**************************@posting.google. com>...

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

Try things more like this:

CREATE VIEW Driver_2_invoice (description, from_date, amount,
currency)
AS
SELECT D1.description,
B1.from_date,
(B1.active_accounts * F1.fee_rate)/12.00,
D1.currency
FROM BillingReports AS B1, Drivers AS D1, Fee AS F1
WHERE F1.driver_id = 2
AND D1.driver_id = 2;

But did you notice that "BillingReports" is CROSS JOIN-ed? When you
use the VIEW, you apply a from_date to it to get the range you want.

and so on for the other UNION-ed queries.

CREATE VIEW Driver_3_invoice (description, from_date, amount,
currency)
AS
SELECT D1.description, B1.from_date,
B1.zero_balance * F1.fee_rate) / 12.00,
D1.currency
FROM BillingReport AS B1, Drivers AS D1, Fee AS F1
WHERE F1.driver_id = 3
AND D1.driver_id = 3;

Etc.

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?

[Joe Celko]

>> I will get a book on SE and follow some
of your pointer. <<

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!

[Erland Sommarskog]

[posted and mailed, please reply in news]

MD (md********@ifdsgroup.com) writes:

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:

CREATE TABLE #sql(id int IDENTITY, sql varchar(8000) NOT NULL)

Now loop over this table and for each row append to two variables:

SELECT @a = '' -- Init
SELECT @b = 'EXEC('
...
SELECT @a = @a + 'DELCARE @sql' + ltrim(str(id)) + ' varchar(8000)
SELECT @sql = ' + sql,
@b = @b + '@sql' + ltrim(str(id)) + ' + '
FROM #sql
WHERE id = @id
...
-- Final
SELECT @b = @b + ''''')'

EXEC(@a + @b)

That is @a + @b builds a an batch that in its turn calls EXEC() to create
your view.

I hope that you by now realise that you have all reason to reconsider your
design.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp