Execute Big Dynamic SQL In Stored Procedure To Create View

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