November 13, 2014 at 3:06 am
I’m hoping that anyone can help me with an error that I’m receiving when I try to alter following stored procedure. I’m trying to add the “WITH CostCenterList” statement so that I can then use that in the two SELECT statements below that.
The error that I’m getting is the following:
Msg 156, Level 15, State 1, Procedure spProfitNLossStandard, Line 54
Incorrect syntax near the keyword 'if'.
Can anyone help me with the syntax?
/****** Object: StoredProcedure [dbo].[spProfitNLossStandard] Script Date: 11/10/2014 1:03:24 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter PROCEDURE [dbo].[spProfitNLossStandard]
@StartDate datetime = null,
@EndDate datetime = null,
@IsClosingEntry tinyint = 0,
@CostCenterID int = 0
AS
BEGIN
declare @MonthStartDate as datetime
declare @MonthEndDate as datetime
declare @YearStartDate as datetime
declare @YearEndDate as datetime
declare @defaultStartDate as datetime
if isdate(@StartDate) = 0
begin
set @YearStartDate = '1900-01-01 00:00:00.000'
end
else
begin
set @YearStartDate = @StartDate
end
if isdate(@EndDate) = 0
begin
set @YearEndDate = getdate()
end
else
begin
set @YearEndDate = @EndDate
end
----First Day of Month
set @MonthStartDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@YearEndDate),0)) + 1
----Last Day of Month
set @MonthEndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@YearEndDate)+1,0))
; WITH CostCenterList (ID,Description,ParentCostCenterID,ParentLevel,SubLevel,ParentDescription)
AS
( SELECT ParentCostCenters.ID,
ParentCostCenters.Description,
ParentCostCenters.ParentCostCenterID,
ParentCostCenters.ID AS ParentLevel,
0 AS SubLevel,
CONVERT(nvarchar(MAX),
ParentCostCenters.Description + '') AS ParentDescription
FROM CostCenters AS ParentCostCenters
WHERE ParentCostCenters.ID = @CostCenterID
UNION ALL
SELECT SubCostCenters.ID,
SubCostCenters.Description,
SubCostCenters.ParentCostCenterID,
SubCostCenters.ParentCostCenterID AS ParentLevel,
CCL.SubLevel + 1 as SubLevel,
CONVERT(nvarchar(MAX),
CCL.ParentDescription + SubCostCenters.Description) AS ParentDescription
FROM CostCenters AS SubCostCenters
INNER JOIN CostCenterList CCL
ON SubCostCenters.ParentCostCenterID = CCL.ID
WHERE SubCostCenters.ParentCostCenterID IS NOT NULL)
--If the user selects “Select Fiscal Date” from the date selector combobox, then the calculation for YearBalance
--should include all transactions (even Closing Entry and Closing Entry Revision transactions)
IF @IsClosingEntry = 1
begin
select GLAccountsID, GLAccountTypeID,GLAccountTypesDescription,GLNumber,GLAccountsDescription, AccountLevel, isnull(ParentGLAccountID,0) ParentGLAccountID,
vwGLAccountsWithLevels.EnumKey GLAccountTypesEnumKey,
--isnull(TotalBalance,0.00) TotalBalance
TotalBalance
from vwGLAccountsWithLevels
left outer join
(select GLAccountID, sum(DebitAmount - CreditAmount) TotalBalance from vwTransactionDetails
where cast(convert(varchar(10),PostingDate,110) as datetime) between cast(convert(varchar(10),@YearStartDate,110) as datetime) and cast(convert(varchar(10),@YearEndDate,110) as datetime)
and ((@CostCenterID = 0) or (@CostCenterID = -1 AND CostCenterID IS NOT NULL) OR (@CostCenterID = -2 AND CostCenterID IS NULL) OR (@CostCenterID > 0 AND CostCenterID IS NOT NULL AND CostCenterID IN (SELECT ID FROM CostCenterList)))
group by GLAccountID) TotalBalance
on vwGLAccountsWithLevels.GLAccountsID = TotalBalance.GLAccountID and IsProfitLoss = 1
--where (TotalBalance is not null)
where GLAccountTypesDescription in ('Income','Cost of Sales','Selling & Operating Expense','General & Admin. Expense','Other Income','Other Expense')
order by GLAccountTypeID,cast(GLNumber as varchar)
end
else
begin
select GLAccountsID, GLAccountTypeID,GLAccountTypesDescription,GLNumber,GLAccountsDescription, AccountLevel, isnull(ParentGLAccountID,0) ParentGLAccountID,
vwGLAccountsWithLevels.EnumKey GLAccountTypesEnumKey,
--isnull(TotalBalance,0.00) TotalBalance
TotalBalance
from vwGLAccountsWithLevels
left outer join
(select GLAccountID, sum(DebitAmount - CreditAmount) TotalBalance from vwTransactionDetails
where cast(convert(varchar(10),PostingDate,110) as datetime) between cast(convert(varchar(10),@YearStartDate,110) as datetime) and cast(convert(varchar(10),@YearEndDate,110) as datetime)
and EnumKey != 'ClosingEntry' and EnumKey != 'ClosingEntryRevision'
and ((@CostCenterID = 0) or (@CostCenterID = -1 AND CostCenterID IS NOT NULL) OR (@CostCenterID = -2 AND CostCenterID IS NULL) OR (@CostCenterID > 0 AND CostCenterID IS NOT NULL AND CostCenterID IN (SELECT ID FROM CostCenterList)))
group by GLAccountID) TotalBalance
on vwGLAccountsWithLevels.GLAccountsID = TotalBalance.GLAccountID and IsProfitLoss = 1
--where (TotalBalance is not null)
where GLAccountTypesDescription in ('Income','Cost of Sales','Selling & Operating Expense','General & Admin. Expense','Other Income','Other Expense')
order by GLAccountTypeID,cast(GLNumber as varchar)
end
END
GO
November 13, 2014 at 3:25 am
The CTE should be followed by a SELECT, INSERT, UPDATE or DELETE statement, not by an IF statement.
ps: the semicolon is a statement terminator, it has no business being there before WITH.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 13, 2014 at 3:58 am
Thank you soo much!!!! I am now doing the following right after defining the CTE
SELECT *
INTO #CostCenterList
FROM CostCenterList
And then referenceing #CostCenterList in the subsequent code.
Thanks once again for quick reply.. GOD Bless you
November 14, 2014 at 2:54 am
Koen Verbeeck (11/13/2014)
The CTE should be followed by a SELECT, INSERT, UPDATE or DELETE statement, not by an IF statement.ps: the semicolon is a statement terminator, it has no business being there before WITH.
The problem is that 90% of developers don't use the ';' on every line of code, I know I don't, especially in a declaration area, so putting the ';' at the start of the WITH has started to become common practice.
Right or wrong it seems to be the trend.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 14, 2014 at 3:54 am
Jason-299789 (11/14/2014)
The problem is that 90% of developers don't use the ';' on every line of code
.Well they should start doing so .The trend over the last few versions is to more and more statements requiring either that they are terminated with a ; ,or that the statement previous is terminated with a ; .Microsoft has suggested that the ; delimiter will eventually be required everywhere .Saying that bad coding practices is a trend doesn't make it any less of a bad practice .With the ;WITH it doesn't teach new devs that the ; is a statement terminator ,it doesn't give a sense of consistency ,of logic .It suggests that in SQL some statement end with it ,some start with it ,most don't need it ,and the only way to go about things is to memorise the requirements ,which is not the case
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2014 at 6:06 am
GilaMonster (11/14/2014)
Jason-299789 (11/14/2014)
The problem is that 90% of developers don't use the ';' on every line of code.Well they should start doing so .The trend over the last few versions is to more and more statements requiring either that they are terminated with a ; ,or that the statement previous is terminated with a ; .Microsoft has suggested that the ; delimiter will eventually be required everywhere .Saying that bad coding practices is a trend doesn't make it any less of a bad practice .With the ;WITH it doesn't teach new devs that the ; is a statement terminator ,it doesn't give a sense of consistency ,of logic .It suggests that in SQL some statement end with it ,some start with it ,most don't need it ,and the only way to go about things is to memorise the requirements ,which is not the case
+1000. SQL Server 2014 states that not terminating statements with a semicolon is deprecated. I say it's about time. http://msdn.microsoft.com/en-us/library/ms143729.aspx
November 14, 2014 at 7:13 am
Jason-299789 (11/14/2014)
Koen Verbeeck (11/13/2014)
The CTE should be followed by a SELECT, INSERT, UPDATE or DELETE statement, not by an IF statement.ps: the semicolon is a statement terminator, it has no business being there before WITH.
The problem is that 90% of developers don't use the ';' on every line of code, I know I don't, especially in a declaration area, so putting the ';' at the start of the WITH has started to become common practice.
Right or wrong it seems to be the trend.
< RANT >
And it is a trend that is wrong and is perpetrated by Microsoft in their own code examples in BOL. BOL clearly states that for a CTE the preceding statement MUST be terminated with a semicolon, then THEY precede the WITH with a semicolon instead of placing it where it belongs.
Can't remember it off the top of my head, but there is another statement added in SQL Server 2014 that states the same requirement, the preceding statement must be terminated with a semicolon.
Then you have the MERGE statement. It clearly states it MUST be terminated with a semicolon. So, combine that with a CTE and what do you have? A statement that people will write with a semicolon at the beginning and end.
The semicolon is a statement terminator, not a statement begininator. Put them where they belong, and the END of the statements.
< /RANT >
November 14, 2014 at 7:51 am
Gail/Lynn/Ed,
I don't disagree, and as Lynn states maybe if MS enforced it so that code wouldn't compile without the use of the statement terminators, like they do in C#/C++ their use would become second nature.
as it is code written without them runs, except in a few notable situations (Merge, and CTE's), so we developers forget that we need them.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 14, 2014 at 8:10 am
Jason-299789 (11/14/2014)
Gail/Lynn/Ed,I don't disagree, and as Lynn states maybe if MS enforced it so that code wouldn't compile without the use of the statement terminators, like they do in C#/C++ their use would become second nature.
as it is code written without them runs, except in a few notable situations (Merge, and CTE's), so we developers forget that we need them.
The problem is that MS won't do it because it will prevent people from migrating as many applications would fail. If you use the semicolon before a CTE as a rule, you'll end up with something like this:
IF 1=2
;WITH CTE AS(
SELECT 1 AS myInt
)
SELECT * FROM CTE
Or being unable to create inline table valued functions with CTEs in them.
November 14, 2014 at 8:27 am
Lynn Pettis (11/14/2014)
Can't remember it off the top of my head, but there is another statement added in SQL Server 2014 that states the same requirement, the preceding statement must be terminated with a semicolon.
THROW;
Amusingly enough, that one doesn't throw errors if the ; is missing (usually), it just behaves unexpectedly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2014 at 8:33 am
I know we're getting a little side tracked, and you give a very good example Luis of why you shouldn't do it.
Final question from me about this, is this a throw back to the old Sybase way of doing things or was it something that MS introduced.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 14, 2014 at 8:36 am
Jason-299789 (11/14/2014)
Gail/Lynn/Ed,I don't disagree, and as Lynn states maybe if MS enforced it so that code wouldn't compile without the use of the statement terminators, like they do in C#/C++ their use would become second nature.
as it is code written without them runs, except in a few notable situations (Merge, and CTE's), so we developers forget that we need them.
They're going to. Eventually. The thing is, they have to put a change like that through the usual 2 (or more) version deprecation cycle because otherwise people would scream blue murder.
Here's a question for you though, if you keep on 'forgetting' to use them, what are you going to do when you upgrade to SQL Server 2018(ish) and suddenly every single stored proc, function, ad-hoc SQL statement won't work because ; are now mandatory?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2014 at 8:56 am
Jason-299789 (11/14/2014)
Gail/Lynn/Ed,I don't disagree, and as Lynn states maybe if MS enforced it so that code wouldn't compile without the use of the statement terminators, like they do in C#/C++ their use would become second nature.
as it is code written without them runs, except in a few notable situations (Merge, and CTE's), so we developers forget that we need them.
If you were to look at most (even I fail at times) of my production code, I terminate all my SQL statements with a semicolon. It is a habit I try to instill in others I work with as well. The best way to do it is with code reviews (I know, what are those!) as you can ensure that statements are properly terminated and hopefully the developers will get used to using them. They can also start adding them to legacy code as it comes up for modifications due to changing requirements.
November 14, 2014 at 9:29 am
Gail,
To be honest people will adapt, yes it will be a shed load of work for developers.
Personally speaking, I'll probably do what I did when I heard that the ORDER BY <ordinal> was being deprecated, reprogram myself to use the actual column name rather than the ordinal position, and start going through scripts as they are amended so leaving only a small amount that falls through the holes.
As Lynn, puts it Code review is key, but I can guarantee that the majority of places that write SQL based systems don't undertake code reviews or check for the semi-colon as a terminator.
MS could help the situation by having its tools like SSMS and SSDT require it at compile time, that way changes would naturally migrate through over time, as you wouldn't be able to test your code without making the changes.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 14, 2014 at 9:35 am
Jason-299789 (11/14/2014)
MS could help the situation by having its tools like SSMS and SSDT require it at compile time.
SSMS doesn't have a T-SQL compiler. When you ask for a piece of T-SQL to be parsed, SSMS sends that T-SQL to the connected SQL Server with a NOEXEC setting.
All SSMS has is a very basic syntax checker that's incredibly easy to confuse (red scwiggly).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply