P
Paul Say
I hope this is the right newsgroup. I have written a program for a database
install. I have a file that contains the T-SQL code to create the database
(I generated the file from SQL2000 Enterprise manager). In the program I
break the file up based on the GO statments within the T-SQL file and
execute each SQL Command seperatly. The program creates most of the objects,
bet it jams up and crashes when try ing to create selected stored
procedures. Below is listed one of the stroed procedures it wont create as
well as part of the code used to break up and execute the T-SQL File.
Are ther limitations on the size of the commandtext string
Or could it be the double quotes in the file that causes the problem if so
how should I handle them
thank you in advance
Paul
Stored Procedure
--------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE RPF_CashFlowRpt_GetAll
@SourceType nvarchar(15),
@ReportType nvarchar(1),
@StartYear Int,
@StartMonth Int,
@ValueColumn nvarchar(25)
as
Declare @Month1 int
Declare @Month2 int
Declare @Month3 int
Declare @Month4 int
Declare @Month5 int
Declare @Month6 int
Declare @Year1 int
Declare @Year2 int
Declare @Year3 int
Declare @Year4 int
Declare @Year5 int
Declare @Year6 int
create table #CashTran2 (
SourceType Nvarchar(15),
SourceTransNo int,
Value1 Float,
Value2 Float,
Value3 Float,
Value4 Float,
Value5 Float,
Value6 Float
)
if @ReportType ='Y'
Begin
Set @Year1 = @StartYear
Set @Year2 = @StartYear + 1
Set @Year3 = @StartYear + 2
Set @Year4 = @StartYear + 3
Set @Year5 = @StartYear + 4
Set @Year6 = @StartYear + 5
if @ValueColumn = "Revenue"
insert into #CashTran2 SELECT sourcetype,sourceTransNo,
SUM(CASE CFYear WHEN @Year1 THEN Revenue ELSE 0 END) AS Value1,
SUM(CASE CFYear WHEN @Year2 THEN Revenue ELSE 0 END) AS Value2,
SUM(CASE CFYear WHEN @Year3 THEN Revenue ELSE 0 END) AS Value3,
SUM(CASE CFYear WHEN @Year4 THEN Revenue ELSE 0 END) AS Value4,
SUM(CASE CFYear WHEN @Year5 THEN Revenue ELSE 0 END) AS Value5,
SUM(CASE CFYear WHEN @Year6 THEN Revenue ELSE 0 END) AS Value6
FROM CashFlowYear
Where (CFYear >= @Year1 AND CFYear <= @Year6) and SourceType = @SourceType
GROUP BY SourceType,SourceTransNo
else if @ValueColumn = "Expenses"
insert into #CashTran2 SELECT sourcetype,sourceTransNo,
SUM(CASE CFYear WHEN @Year1 THEN Expenses ELSE 0 END) AS Value1,
SUM(CASE CFYear WHEN @Year2 THEN Expenses ELSE 0 END) AS Value2,
SUM(CASE CFYear WHEN @Year3 THEN Expenses ELSE 0 END) AS Value3,
SUM(CASE CFYear WHEN @Year4 THEN Expenses ELSE 0 END) AS Value4,
SUM(CASE CFYear WHEN @Year5 THEN Expenses ELSE 0 END) AS Value5,
SUM(CASE CFYear WHEN @Year6 THEN Expenses ELSE 0 END) AS Value6
FROM CashFlowYear
Where (CFYear >= @Year1 AND CFYear <= @Year6) and SourceType = @SourceType
GROUP BY SourceType,SourceTransNo
else if @ValueColumn = "Net Profit"
insert into #CashTran2 SELECT sourcetype,sourceTransNo,
SUM(CASE CFYear WHEN @Year1 THEN (Revenue - Expenses) ELSE 0 END) AS Value1,
SUM(CASE CFYear WHEN @Year2 THEN (Revenue - Expenses) ELSE 0 END) AS Value2,
SUM(CASE CFYear WHEN @Year3 THEN (Revenue - Expenses) ELSE 0 END) AS Value3,
SUM(CASE CFYear WHEN @Year4 THEN (Revenue - Expenses) ELSE 0 END) AS Value4,
SUM(CASE CFYear WHEN @Year5 THEN (Revenue - Expenses) ELSE 0 END) AS Value5,
SUM(CASE CFYear WHEN @Year6 THEN (Revenue - Expenses) ELSE 0 END) AS Value6
FROM CashFlowYear
Where (CFYear >= @Year1 AND CFYear <= @Year6) and SourceType = @SourceType
GROUP BY SourceType,SourceTransNo
else if @ValueColumn = "To Receive"
insert into #CashTran2 SELECT sourcetype,sourceTransNo,
SUM(CASE CFYear WHEN @Year1 THEN ToReceive ELSE 0 END) AS Value1,
SUM(CASE CFYear WHEN @Year2 THEN ToReceive ELSE 0 END) AS Value2,
SUM(CASE CFYear WHEN @Year3 THEN ToReceive ELSE 0 END) AS Value3,
SUM(CASE CFYear WHEN @Year4 THEN ToReceive ELSE 0 END) AS Value4,
SUM(CASE CFYear WHEN @Year5 THEN ToReceive ELSE 0 END) AS Value5,
SUM(CASE CFYear WHEN @Year6 THEN ToReceive ELSE 0 END) AS Value6
FROM CashFlowYear
Where (CFYear >= @Year1 AND CFYear <= @Year6) and SourceType = @SourceType
GROUP BY SourceType,SourceTransNo
else if @ValueColumn = "To Pay"
insert into #CashTran2 SELECT sourcetype,sourceTransNo,
SUM(CASE CFYear WHEN @Year1 THEN ToPay ELSE 0 END) AS Value1,
SUM(CASE CFYear WHEN @Year2 THEN ToPay ELSE 0 END) AS Value2,
SUM(CASE CFYear WHEN @Year3 THEN ToPay ELSE 0 END) AS Value3,
SUM(CASE CFYear WHEN @Year4 THEN ToPay ELSE 0 END) AS Value4,
SUM(CASE CFYear WHEN @Year5 THEN ToPay ELSE 0 END) AS Value5,
SUM(CASE CFYear WHEN @Year6 THEN ToPay ELSE 0 END) AS Value6
FROM CashFlowYear
Where (CFYear >= @Year1 AND CFYear <= @Year6) and SourceType = @SourceType
GROUP BY SourceType,SourceTransNo
else if @ValueColumn = "Net Cash"
insert into #CashTran2 SELECT sourcetype,sourceTransNo,
SUM(CASE CFYear WHEN @Year1 THEN (ToReceive-ToPay) ELSE 0 END) AS Value1,
SUM(CASE CFYear WHEN @Year2 THEN (ToReceive-ToPay) ELSE 0 END) AS Value2,
SUM(CASE CFYear WHEN @Year3 THEN (ToReceive-ToPay) ELSE 0 END) AS Value3,
SUM(CASE CFYear WHEN @Year4 THEN (ToReceive-ToPay) ELSE 0 END) AS Value4,
SUM(CASE CFYear WHEN @Year5 THEN (ToReceive-ToPay) ELSE 0 END) AS Value5,
SUM(CASE CFYear WHEN @Year6 THEN (ToReceive-ToPay) ELSE 0 END) AS Value6
FROM CashFlowYear
Where (CFYear >= @Year1 AND CFYear <= @Year6) and SourceType = @SourceType
GROUP BY SourceType,SourceTransNo
End
Else If @ReportType = 'M'
Begin
Set @Month1 = @StartMonth
Set @Year1 = @StartYear
Set @Month2 = @Month1 + 1
Set @Year2 = @Year1
if @Month2 > 12
Begin
Set @Month2 = @Month2 - 12
Set @Year2 = @Year1 + 1
end
Set @Month3 = @Month2 + 1
Set @Year3 = @Year2
if @Month3 > 12
Begin
Set @Month3 = @Month3 - 12
Set @Year3 = @Year2 + 1
end
Set @Month4 = @Month3 + 1
Set @Year4 = @Year3
if @Month4 > 12
Begin
Set @Month4 = @Month4 - 12
Set @Year4 = @Year3 + 1
end
Set @Month5 = @Month4 + 1
Set @Year5 = @Year4
if @Month5 > 12
Begin
Set @Month5 = @Month5 - 12
Set @Year5 = @Year4 + 1
end
Set @Month6 = @Month5 + 1
Set @Year6 = @Year5
if @Month6 > 12
Begin
Set @Month6 = @Month6 - 12
Set @Year6 = @Year5 + 1
end
if @ValueColumn = "Revenue"
insert into #CashTran2 SELECT y.sourcetype,y.sourceTransNo,
SUM(CASE CFMonth WHEN @Month1 THEN m.Revenue ELSE 0 END) AS Value1,
SUM(CASE CFMonth WHEN @Month2 THEN m.Revenue ELSE 0 END) AS Value2,
SUM(CASE CFMonth WHEN @Month3 THEN m.Revenue ELSE 0 END) AS Value3,
SUM(CASE CFMonth WHEN @Month4 THEN m.Revenue ELSE 0 END) AS Value4,
SUM(CASE CFMonth WHEN @Month5 THEN m.Revenue ELSE 0 END) AS Value5,
SUM(CASE CFMonth WHEN @Month6 THEN m.Revenue ELSE 0 END) AS Value6
FROM CashFlowMonth m
JOIN CashFlowYear y ON m.CFYTransNo = y.TransNo
Where ((y.CFYear = @Year1 AND m.CFMonth = @Month1) OR (y.CFYear = @Year2 AND
m.CFMonth = @Month2) OR (y.CFYear = @Year3 AND m.CFMonth = @Month3) OR
(y.CFYear = @Year4 AND m.CFMonth = @Month4) OR (y.CFYear = @Year5 AND
m.CFMonth = @Month5) OR (y.CFYear = @Year6 AND m.CFMonth = @Month6) ) and
y.SourceType = @SourceType
GROUP BY y.SourceType,y.SourceTransNo
else if @ValueColumn = "Expenses"
insert into #CashTran2 SELECT y.sourcetype,y.sourceTransNo,
SUM(CASE CFMonth WHEN @Month1 THEN m.Expenses ELSE 0 END) AS Value1,
SUM(CASE CFMonth WHEN @Month2 THEN m.Expenses ELSE 0 END) AS Value2,
SUM(CASE CFMonth WHEN @Month3 THEN m.Expenses ELSE 0 END) AS Value3,
SUM(CASE CFMonth WHEN @Month4 THEN m.Expenses ELSE 0 END) AS Value4,
SUM(CASE CFMonth WHEN @Month5 THEN m.Expenses ELSE 0 END) AS Value5,
SUM(CASE CFMonth WHEN @Month6 THEN m.Expenses ELSE 0 END) AS Value6
FROM CashFlowMonth m
JOIN CashFlowYear y ON m.CFYTransNo = y.TransNo
Where ((y.CFYear = @Year1 AND m.CFMonth = @Month1) OR (y.CFYear = @Year2 AND
m.CFMonth = @Month2) OR (y.CFYear = @Year3 AND m.CFMonth = @Month3) OR
(y.CFYear = @Year4 AND m.CFMonth = @Month4) OR (y.CFYear = @Year5 AND
m.CFMonth = @Month5) OR (y.CFYear = @Year6 AND m.CFMonth = @Month6) ) and
y.SourceType = @SourceType
GROUP BY y.SourceType,y.SourceTransNo
else if @ValueColumn = "To Pay"
insert into #CashTran2 SELECT y.sourcetype,y.sourceTransNo,
SUM(CASE CFMonth WHEN @Month1 THEN m.ToPay ELSE 0 END) AS Value1,
SUM(CASE CFMonth WHEN @Month2 THEN m.ToPay ELSE 0 END) AS Value2,
SUM(CASE CFMonth WHEN @Month3 THEN m.ToPay ELSE 0 END) AS Value3,
SUM(CASE CFMonth WHEN @Month4 THEN m.ToPay ELSE 0 END) AS Value4,
SUM(CASE CFMonth WHEN @Month5 THEN m.ToPay ELSE 0 END) AS Value5,
SUM(CASE CFMonth WHEN @Month6 THEN m.ToPay ELSE 0 END) AS Value6
FROM CashFlowMonth m
JOIN CashFlowYear y ON m.CFYTransNo = y.TransNo
Where ((y.CFYear = @Year1 AND m.CFMonth = @Month1) OR (y.CFYear = @Year2 AND
m.CFMonth = @Month2) OR (y.CFYear = @Year3 AND m.CFMonth = @Month3) OR
(y.CFYear = @Year4 AND m.CFMonth = @Month4) OR (y.CFYear = @Year5 AND
m.CFMonth = @Month5) OR (y.CFYear = @Year6 AND m.CFMonth = @Month6) ) and
y.SourceType = @SourceType
GROUP BY y.SourceType,y.SourceTransNo
else if @ValueColumn = "To Receive"
insert into #CashTran2 SELECT y.sourcetype,y.sourceTransNo,
SUM(CASE CFMonth WHEN @Month1 THEN m.ToReceive ELSE 0 END) AS Value1,
SUM(CASE CFMonth WHEN @Month2 THEN m.ToReceive ELSE 0 END) AS Value2,
SUM(CASE CFMonth WHEN @Month3 THEN m.ToReceive ELSE 0 END) AS Value3,
SUM(CASE CFMonth WHEN @Month4 THEN m.ToReceive ELSE 0 END) AS Value4,
SUM(CASE CFMonth WHEN @Month5 THEN m.ToReceive ELSE 0 END) AS Value5,
SUM(CASE CFMonth WHEN @Month6 THEN m.ToReceive ELSE 0 END) AS Value6
FROM CashFlowMonth m
JOIN CashFlowYear y ON m.CFYTransNo = y.TransNo
Where ((y.CFYear = @Year1 AND m.CFMonth = @Month1) OR (y.CFYear = @Year2 AND
m.CFMonth = @Month2) OR (y.CFYear = @Year3 AND m.CFMonth = @Month3) OR
(y.CFYear = @Year4 AND m.CFMonth = @Month4) OR (y.CFYear = @Year5 AND
m.CFMonth = @Month5) OR (y.CFYear = @Year6 AND m.CFMonth = @Month6) ) and
y.SourceType = @SourceType
GROUP BY y.SourceType,y.SourceTransNo
else if @ValueColumn = "Net Profit"
insert into #CashTran2 SELECT y.sourcetype,y.sourceTransNo,
SUM(CASE CFMonth WHEN @Month1 THEN (m.Revenue - m.Expenses) ELSE 0 END) AS
Value1,
SUM(CASE CFMonth WHEN @Month2 THEN (m.Revenue - m.Expenses) ELSE 0 END) AS
Value2,
SUM(CASE CFMonth WHEN @Month3 THEN (m.Revenue - m.Expenses) ELSE 0 END) AS
Value3,
SUM(CASE CFMonth WHEN @Month4 THEN (m.Revenue - m.Expenses) ELSE 0 END) AS
Value4,
SUM(CASE CFMonth WHEN @Month5 THEN (m.Revenue - m.Expenses) ELSE 0 END) AS
Value5,
SUM(CASE CFMonth WHEN @Month6 THEN (m.Revenue - m.Expenses) ELSE 0 END) AS
Value6
FROM CashFlowMonth m
JOIN CashFlowYear y ON m.CFYTransNo = y.TransNo
Where ((y.CFYear = @Year1 AND m.CFMonth = @Month1) OR (y.CFYear = @Year2 AND
m.CFMonth = @Month2) OR (y.CFYear = @Year3 AND m.CFMonth = @Month3) OR
(y.CFYear = @Year4 AND m.CFMonth = @Month4) OR (y.CFYear = @Year5 AND
m.CFMonth = @Month5) OR (y.CFYear = @Year6 AND m.CFMonth = @Month6) ) and
y.SourceType = @SourceType
GROUP BY y.SourceType,y.SourceTransNo
else if @ValueColumn = "Net Cash"
insert into #CashTran2 SELECT y.sourcetype,y.sourceTransNo,
SUM(CASE CFMonth WHEN @Month1 THEN (m.ToReceive - m.ToPay) ELSE 0 END) AS
Value1,
SUM(CASE CFMonth WHEN @Month2 THEN (m.ToReceive - m.ToPay) ELSE 0 END) AS
Value2,
SUM(CASE CFMonth WHEN @Month3 THEN (m.ToReceive - m.ToPay) ELSE 0 END) AS
Value3,
SUM(CASE CFMonth WHEN @Month4 THEN (m.ToReceive - m.ToPay) ELSE 0 END) AS
Value4,
SUM(CASE CFMonth WHEN @Month5 THEN (m.ToReceive - m.ToPay) ELSE 0 END) AS
Value5,
SUM(CASE CFMonth WHEN @Month6 THEN (m.ToReceive - m.ToPay) ELSE 0 END) AS
Value6
FROM CashFlowMonth m
JOIN CashFlowYear y ON m.CFYTransNo = y.TransNo
Where ((y.CFYear = @Year1 AND m.CFMonth = @Month1) OR (y.CFYear = @Year2 AND
m.CFMonth = @Month2) OR (y.CFYear = @Year3 AND m.CFMonth = @Month3) OR
(y.CFYear = @Year4 AND m.CFMonth = @Month4) OR (y.CFYear = @Year5 AND
m.CFMonth = @Month5) OR (y.CFYear = @Year6 AND m.CFMonth = @Month6) ) and
y.SourceType = @SourceType
GROUP BY y.SourceType,y.SourceTransNo
End
Select ct.*,st.SourceId, st.[Description] From
#CashTran2 ct Join
(
select 'Job' as SourceType, Transno as SourceTransNo, Job as SourceId,
[Description] From Job
Union
select 'Opportunity' as SourceType, Transno as SourceTransNo, OpportunityId,
[Description] From Opportunity
Union
select 'Sundry' as SourceType, Transno as SourceTransNo, GLAccount,
[Description] From GLAccount
) st
on ct.SourceType = st.SourceType and ct.SourceTransNo = st.SourceTransNo
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CODE SEGMENT
-------------------------------------------------------------------------------------------------
Protected Sub ExecuteScript(ByVal strDBName As String, ByVal ScriptFile As
String)
Dim scriptString As StreamReader = GetSqlScript(ScriptFile)
Dim strCommand As String = String.Empty
Dim strLine As String = String.Empty
strLine = scriptString.ReadLine()
While Not strLine Is Nothing
If Trim(strLine) = "GO" Then
ExecuteSql(strDBName, strCommand)
strCommand = ""
Else
If Len(Trim(strLine)) > 1 Then
If Len(strCommand) > 1 Then
strCommand = strCommand & vbCrLf
End If
strCommand = strCommand
End If
End If
strLine = scriptString.ReadLine()
End While
scriptString.Close()
End Sub
Private Function GetSqlScript(ByVal Name As String) As StreamReader
Try
' Gets the current assembly.
Dim Asm As [Assembly] = [Assembly].GetExecutingAssembly()
' Resources are named using a fully qualified name.
Dim strm As Stream = Asm.GetManifestResourceStream(Asm.GetName().Name + "."
+ Name)
' Reads the contents of the embedded file.
Dim reader As StreamReader = New StreamReader(strm)
Return reader
Catch ex As Exception
MsgBox("In GetSQL: " & ex.Message)
Throw ex
End Try
End Function
Private Sub ExecuteSql(ByVal DatabaseName As String, ByVal Sql As String)
Dim Command As New SqlClient.SqlCommand(Sql, sqlConnection1)
Command.Connection.Open()
Command.Connection.ChangeDatabase(DatabaseName)
Try
Command.ExecuteNonQuery()
Finally
' Finally, blocks are a great way to ensure that the connection
' is always closed.
Command.Connection.Close()
End Try
End Sub
install. I have a file that contains the T-SQL code to create the database
(I generated the file from SQL2000 Enterprise manager). In the program I
break the file up based on the GO statments within the T-SQL file and
execute each SQL Command seperatly. The program creates most of the objects,
bet it jams up and crashes when try ing to create selected stored
procedures. Below is listed one of the stroed procedures it wont create as
well as part of the code used to break up and execute the T-SQL File.
Are ther limitations on the size of the commandtext string
Or could it be the double quotes in the file that causes the problem if so
how should I handle them
thank you in advance
Paul
Stored Procedure
--------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE RPF_CashFlowRpt_GetAll
@SourceType nvarchar(15),
@ReportType nvarchar(1),
@StartYear Int,
@StartMonth Int,
@ValueColumn nvarchar(25)
as
Declare @Month1 int
Declare @Month2 int
Declare @Month3 int
Declare @Month4 int
Declare @Month5 int
Declare @Month6 int
Declare @Year1 int
Declare @Year2 int
Declare @Year3 int
Declare @Year4 int
Declare @Year5 int
Declare @Year6 int
create table #CashTran2 (
SourceType Nvarchar(15),
SourceTransNo int,
Value1 Float,
Value2 Float,
Value3 Float,
Value4 Float,
Value5 Float,
Value6 Float
)
if @ReportType ='Y'
Begin
Set @Year1 = @StartYear
Set @Year2 = @StartYear + 1
Set @Year3 = @StartYear + 2
Set @Year4 = @StartYear + 3
Set @Year5 = @StartYear + 4
Set @Year6 = @StartYear + 5
if @ValueColumn = "Revenue"
insert into #CashTran2 SELECT sourcetype,sourceTransNo,
SUM(CASE CFYear WHEN @Year1 THEN Revenue ELSE 0 END) AS Value1,
SUM(CASE CFYear WHEN @Year2 THEN Revenue ELSE 0 END) AS Value2,
SUM(CASE CFYear WHEN @Year3 THEN Revenue ELSE 0 END) AS Value3,
SUM(CASE CFYear WHEN @Year4 THEN Revenue ELSE 0 END) AS Value4,
SUM(CASE CFYear WHEN @Year5 THEN Revenue ELSE 0 END) AS Value5,
SUM(CASE CFYear WHEN @Year6 THEN Revenue ELSE 0 END) AS Value6
FROM CashFlowYear
Where (CFYear >= @Year1 AND CFYear <= @Year6) and SourceType = @SourceType
GROUP BY SourceType,SourceTransNo
else if @ValueColumn = "Expenses"
insert into #CashTran2 SELECT sourcetype,sourceTransNo,
SUM(CASE CFYear WHEN @Year1 THEN Expenses ELSE 0 END) AS Value1,
SUM(CASE CFYear WHEN @Year2 THEN Expenses ELSE 0 END) AS Value2,
SUM(CASE CFYear WHEN @Year3 THEN Expenses ELSE 0 END) AS Value3,
SUM(CASE CFYear WHEN @Year4 THEN Expenses ELSE 0 END) AS Value4,
SUM(CASE CFYear WHEN @Year5 THEN Expenses ELSE 0 END) AS Value5,
SUM(CASE CFYear WHEN @Year6 THEN Expenses ELSE 0 END) AS Value6
FROM CashFlowYear
Where (CFYear >= @Year1 AND CFYear <= @Year6) and SourceType = @SourceType
GROUP BY SourceType,SourceTransNo
else if @ValueColumn = "Net Profit"
insert into #CashTran2 SELECT sourcetype,sourceTransNo,
SUM(CASE CFYear WHEN @Year1 THEN (Revenue - Expenses) ELSE 0 END) AS Value1,
SUM(CASE CFYear WHEN @Year2 THEN (Revenue - Expenses) ELSE 0 END) AS Value2,
SUM(CASE CFYear WHEN @Year3 THEN (Revenue - Expenses) ELSE 0 END) AS Value3,
SUM(CASE CFYear WHEN @Year4 THEN (Revenue - Expenses) ELSE 0 END) AS Value4,
SUM(CASE CFYear WHEN @Year5 THEN (Revenue - Expenses) ELSE 0 END) AS Value5,
SUM(CASE CFYear WHEN @Year6 THEN (Revenue - Expenses) ELSE 0 END) AS Value6
FROM CashFlowYear
Where (CFYear >= @Year1 AND CFYear <= @Year6) and SourceType = @SourceType
GROUP BY SourceType,SourceTransNo
else if @ValueColumn = "To Receive"
insert into #CashTran2 SELECT sourcetype,sourceTransNo,
SUM(CASE CFYear WHEN @Year1 THEN ToReceive ELSE 0 END) AS Value1,
SUM(CASE CFYear WHEN @Year2 THEN ToReceive ELSE 0 END) AS Value2,
SUM(CASE CFYear WHEN @Year3 THEN ToReceive ELSE 0 END) AS Value3,
SUM(CASE CFYear WHEN @Year4 THEN ToReceive ELSE 0 END) AS Value4,
SUM(CASE CFYear WHEN @Year5 THEN ToReceive ELSE 0 END) AS Value5,
SUM(CASE CFYear WHEN @Year6 THEN ToReceive ELSE 0 END) AS Value6
FROM CashFlowYear
Where (CFYear >= @Year1 AND CFYear <= @Year6) and SourceType = @SourceType
GROUP BY SourceType,SourceTransNo
else if @ValueColumn = "To Pay"
insert into #CashTran2 SELECT sourcetype,sourceTransNo,
SUM(CASE CFYear WHEN @Year1 THEN ToPay ELSE 0 END) AS Value1,
SUM(CASE CFYear WHEN @Year2 THEN ToPay ELSE 0 END) AS Value2,
SUM(CASE CFYear WHEN @Year3 THEN ToPay ELSE 0 END) AS Value3,
SUM(CASE CFYear WHEN @Year4 THEN ToPay ELSE 0 END) AS Value4,
SUM(CASE CFYear WHEN @Year5 THEN ToPay ELSE 0 END) AS Value5,
SUM(CASE CFYear WHEN @Year6 THEN ToPay ELSE 0 END) AS Value6
FROM CashFlowYear
Where (CFYear >= @Year1 AND CFYear <= @Year6) and SourceType = @SourceType
GROUP BY SourceType,SourceTransNo
else if @ValueColumn = "Net Cash"
insert into #CashTran2 SELECT sourcetype,sourceTransNo,
SUM(CASE CFYear WHEN @Year1 THEN (ToReceive-ToPay) ELSE 0 END) AS Value1,
SUM(CASE CFYear WHEN @Year2 THEN (ToReceive-ToPay) ELSE 0 END) AS Value2,
SUM(CASE CFYear WHEN @Year3 THEN (ToReceive-ToPay) ELSE 0 END) AS Value3,
SUM(CASE CFYear WHEN @Year4 THEN (ToReceive-ToPay) ELSE 0 END) AS Value4,
SUM(CASE CFYear WHEN @Year5 THEN (ToReceive-ToPay) ELSE 0 END) AS Value5,
SUM(CASE CFYear WHEN @Year6 THEN (ToReceive-ToPay) ELSE 0 END) AS Value6
FROM CashFlowYear
Where (CFYear >= @Year1 AND CFYear <= @Year6) and SourceType = @SourceType
GROUP BY SourceType,SourceTransNo
End
Else If @ReportType = 'M'
Begin
Set @Month1 = @StartMonth
Set @Year1 = @StartYear
Set @Month2 = @Month1 + 1
Set @Year2 = @Year1
if @Month2 > 12
Begin
Set @Month2 = @Month2 - 12
Set @Year2 = @Year1 + 1
end
Set @Month3 = @Month2 + 1
Set @Year3 = @Year2
if @Month3 > 12
Begin
Set @Month3 = @Month3 - 12
Set @Year3 = @Year2 + 1
end
Set @Month4 = @Month3 + 1
Set @Year4 = @Year3
if @Month4 > 12
Begin
Set @Month4 = @Month4 - 12
Set @Year4 = @Year3 + 1
end
Set @Month5 = @Month4 + 1
Set @Year5 = @Year4
if @Month5 > 12
Begin
Set @Month5 = @Month5 - 12
Set @Year5 = @Year4 + 1
end
Set @Month6 = @Month5 + 1
Set @Year6 = @Year5
if @Month6 > 12
Begin
Set @Month6 = @Month6 - 12
Set @Year6 = @Year5 + 1
end
if @ValueColumn = "Revenue"
insert into #CashTran2 SELECT y.sourcetype,y.sourceTransNo,
SUM(CASE CFMonth WHEN @Month1 THEN m.Revenue ELSE 0 END) AS Value1,
SUM(CASE CFMonth WHEN @Month2 THEN m.Revenue ELSE 0 END) AS Value2,
SUM(CASE CFMonth WHEN @Month3 THEN m.Revenue ELSE 0 END) AS Value3,
SUM(CASE CFMonth WHEN @Month4 THEN m.Revenue ELSE 0 END) AS Value4,
SUM(CASE CFMonth WHEN @Month5 THEN m.Revenue ELSE 0 END) AS Value5,
SUM(CASE CFMonth WHEN @Month6 THEN m.Revenue ELSE 0 END) AS Value6
FROM CashFlowMonth m
JOIN CashFlowYear y ON m.CFYTransNo = y.TransNo
Where ((y.CFYear = @Year1 AND m.CFMonth = @Month1) OR (y.CFYear = @Year2 AND
m.CFMonth = @Month2) OR (y.CFYear = @Year3 AND m.CFMonth = @Month3) OR
(y.CFYear = @Year4 AND m.CFMonth = @Month4) OR (y.CFYear = @Year5 AND
m.CFMonth = @Month5) OR (y.CFYear = @Year6 AND m.CFMonth = @Month6) ) and
y.SourceType = @SourceType
GROUP BY y.SourceType,y.SourceTransNo
else if @ValueColumn = "Expenses"
insert into #CashTran2 SELECT y.sourcetype,y.sourceTransNo,
SUM(CASE CFMonth WHEN @Month1 THEN m.Expenses ELSE 0 END) AS Value1,
SUM(CASE CFMonth WHEN @Month2 THEN m.Expenses ELSE 0 END) AS Value2,
SUM(CASE CFMonth WHEN @Month3 THEN m.Expenses ELSE 0 END) AS Value3,
SUM(CASE CFMonth WHEN @Month4 THEN m.Expenses ELSE 0 END) AS Value4,
SUM(CASE CFMonth WHEN @Month5 THEN m.Expenses ELSE 0 END) AS Value5,
SUM(CASE CFMonth WHEN @Month6 THEN m.Expenses ELSE 0 END) AS Value6
FROM CashFlowMonth m
JOIN CashFlowYear y ON m.CFYTransNo = y.TransNo
Where ((y.CFYear = @Year1 AND m.CFMonth = @Month1) OR (y.CFYear = @Year2 AND
m.CFMonth = @Month2) OR (y.CFYear = @Year3 AND m.CFMonth = @Month3) OR
(y.CFYear = @Year4 AND m.CFMonth = @Month4) OR (y.CFYear = @Year5 AND
m.CFMonth = @Month5) OR (y.CFYear = @Year6 AND m.CFMonth = @Month6) ) and
y.SourceType = @SourceType
GROUP BY y.SourceType,y.SourceTransNo
else if @ValueColumn = "To Pay"
insert into #CashTran2 SELECT y.sourcetype,y.sourceTransNo,
SUM(CASE CFMonth WHEN @Month1 THEN m.ToPay ELSE 0 END) AS Value1,
SUM(CASE CFMonth WHEN @Month2 THEN m.ToPay ELSE 0 END) AS Value2,
SUM(CASE CFMonth WHEN @Month3 THEN m.ToPay ELSE 0 END) AS Value3,
SUM(CASE CFMonth WHEN @Month4 THEN m.ToPay ELSE 0 END) AS Value4,
SUM(CASE CFMonth WHEN @Month5 THEN m.ToPay ELSE 0 END) AS Value5,
SUM(CASE CFMonth WHEN @Month6 THEN m.ToPay ELSE 0 END) AS Value6
FROM CashFlowMonth m
JOIN CashFlowYear y ON m.CFYTransNo = y.TransNo
Where ((y.CFYear = @Year1 AND m.CFMonth = @Month1) OR (y.CFYear = @Year2 AND
m.CFMonth = @Month2) OR (y.CFYear = @Year3 AND m.CFMonth = @Month3) OR
(y.CFYear = @Year4 AND m.CFMonth = @Month4) OR (y.CFYear = @Year5 AND
m.CFMonth = @Month5) OR (y.CFYear = @Year6 AND m.CFMonth = @Month6) ) and
y.SourceType = @SourceType
GROUP BY y.SourceType,y.SourceTransNo
else if @ValueColumn = "To Receive"
insert into #CashTran2 SELECT y.sourcetype,y.sourceTransNo,
SUM(CASE CFMonth WHEN @Month1 THEN m.ToReceive ELSE 0 END) AS Value1,
SUM(CASE CFMonth WHEN @Month2 THEN m.ToReceive ELSE 0 END) AS Value2,
SUM(CASE CFMonth WHEN @Month3 THEN m.ToReceive ELSE 0 END) AS Value3,
SUM(CASE CFMonth WHEN @Month4 THEN m.ToReceive ELSE 0 END) AS Value4,
SUM(CASE CFMonth WHEN @Month5 THEN m.ToReceive ELSE 0 END) AS Value5,
SUM(CASE CFMonth WHEN @Month6 THEN m.ToReceive ELSE 0 END) AS Value6
FROM CashFlowMonth m
JOIN CashFlowYear y ON m.CFYTransNo = y.TransNo
Where ((y.CFYear = @Year1 AND m.CFMonth = @Month1) OR (y.CFYear = @Year2 AND
m.CFMonth = @Month2) OR (y.CFYear = @Year3 AND m.CFMonth = @Month3) OR
(y.CFYear = @Year4 AND m.CFMonth = @Month4) OR (y.CFYear = @Year5 AND
m.CFMonth = @Month5) OR (y.CFYear = @Year6 AND m.CFMonth = @Month6) ) and
y.SourceType = @SourceType
GROUP BY y.SourceType,y.SourceTransNo
else if @ValueColumn = "Net Profit"
insert into #CashTran2 SELECT y.sourcetype,y.sourceTransNo,
SUM(CASE CFMonth WHEN @Month1 THEN (m.Revenue - m.Expenses) ELSE 0 END) AS
Value1,
SUM(CASE CFMonth WHEN @Month2 THEN (m.Revenue - m.Expenses) ELSE 0 END) AS
Value2,
SUM(CASE CFMonth WHEN @Month3 THEN (m.Revenue - m.Expenses) ELSE 0 END) AS
Value3,
SUM(CASE CFMonth WHEN @Month4 THEN (m.Revenue - m.Expenses) ELSE 0 END) AS
Value4,
SUM(CASE CFMonth WHEN @Month5 THEN (m.Revenue - m.Expenses) ELSE 0 END) AS
Value5,
SUM(CASE CFMonth WHEN @Month6 THEN (m.Revenue - m.Expenses) ELSE 0 END) AS
Value6
FROM CashFlowMonth m
JOIN CashFlowYear y ON m.CFYTransNo = y.TransNo
Where ((y.CFYear = @Year1 AND m.CFMonth = @Month1) OR (y.CFYear = @Year2 AND
m.CFMonth = @Month2) OR (y.CFYear = @Year3 AND m.CFMonth = @Month3) OR
(y.CFYear = @Year4 AND m.CFMonth = @Month4) OR (y.CFYear = @Year5 AND
m.CFMonth = @Month5) OR (y.CFYear = @Year6 AND m.CFMonth = @Month6) ) and
y.SourceType = @SourceType
GROUP BY y.SourceType,y.SourceTransNo
else if @ValueColumn = "Net Cash"
insert into #CashTran2 SELECT y.sourcetype,y.sourceTransNo,
SUM(CASE CFMonth WHEN @Month1 THEN (m.ToReceive - m.ToPay) ELSE 0 END) AS
Value1,
SUM(CASE CFMonth WHEN @Month2 THEN (m.ToReceive - m.ToPay) ELSE 0 END) AS
Value2,
SUM(CASE CFMonth WHEN @Month3 THEN (m.ToReceive - m.ToPay) ELSE 0 END) AS
Value3,
SUM(CASE CFMonth WHEN @Month4 THEN (m.ToReceive - m.ToPay) ELSE 0 END) AS
Value4,
SUM(CASE CFMonth WHEN @Month5 THEN (m.ToReceive - m.ToPay) ELSE 0 END) AS
Value5,
SUM(CASE CFMonth WHEN @Month6 THEN (m.ToReceive - m.ToPay) ELSE 0 END) AS
Value6
FROM CashFlowMonth m
JOIN CashFlowYear y ON m.CFYTransNo = y.TransNo
Where ((y.CFYear = @Year1 AND m.CFMonth = @Month1) OR (y.CFYear = @Year2 AND
m.CFMonth = @Month2) OR (y.CFYear = @Year3 AND m.CFMonth = @Month3) OR
(y.CFYear = @Year4 AND m.CFMonth = @Month4) OR (y.CFYear = @Year5 AND
m.CFMonth = @Month5) OR (y.CFYear = @Year6 AND m.CFMonth = @Month6) ) and
y.SourceType = @SourceType
GROUP BY y.SourceType,y.SourceTransNo
End
Select ct.*,st.SourceId, st.[Description] From
#CashTran2 ct Join
(
select 'Job' as SourceType, Transno as SourceTransNo, Job as SourceId,
[Description] From Job
Union
select 'Opportunity' as SourceType, Transno as SourceTransNo, OpportunityId,
[Description] From Opportunity
Union
select 'Sundry' as SourceType, Transno as SourceTransNo, GLAccount,
[Description] From GLAccount
) st
on ct.SourceType = st.SourceType and ct.SourceTransNo = st.SourceTransNo
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CODE SEGMENT
-------------------------------------------------------------------------------------------------
Protected Sub ExecuteScript(ByVal strDBName As String, ByVal ScriptFile As
String)
Dim scriptString As StreamReader = GetSqlScript(ScriptFile)
Dim strCommand As String = String.Empty
Dim strLine As String = String.Empty
strLine = scriptString.ReadLine()
While Not strLine Is Nothing
If Trim(strLine) = "GO" Then
ExecuteSql(strDBName, strCommand)
strCommand = ""
Else
If Len(Trim(strLine)) > 1 Then
If Len(strCommand) > 1 Then
strCommand = strCommand & vbCrLf
End If
strCommand = strCommand
End If
End If
strLine = scriptString.ReadLine()
End While
scriptString.Close()
End Sub
Private Function GetSqlScript(ByVal Name As String) As StreamReader
Try
' Gets the current assembly.
Dim Asm As [Assembly] = [Assembly].GetExecutingAssembly()
' Resources are named using a fully qualified name.
Dim strm As Stream = Asm.GetManifestResourceStream(Asm.GetName().Name + "."
+ Name)
' Reads the contents of the embedded file.
Dim reader As StreamReader = New StreamReader(strm)
Return reader
Catch ex As Exception
MsgBox("In GetSQL: " & ex.Message)
Throw ex
End Try
End Function
Private Sub ExecuteSql(ByVal DatabaseName As String, ByVal Sql As String)
Dim Command As New SqlClient.SqlCommand(Sql, sqlConnection1)
Command.Connection.Open()
Command.Connection.ChangeDatabase(DatabaseName)
Try
Command.ExecuteNonQuery()
Finally
' Finally, blocks are a great way to ensure that the connection
' is always closed.
Command.Connection.Close()
End Try
End Sub