question

DSM avatar image
DSM asked

Error message in sp.

sp below give error in sql server 2005 as Msg 102, Level 15, State 1, Line 51 Incorrect syntax near '@end_date'. May please suggest solution. Thanks in advance. Create PROC [dbo].[SP_CategoryOutstanding] @START_DATE As datetime, @END_DATE As datetime As DECLARE @SQLSTR VARCHAR(MAX) begin SET @SQLSTR=' if object_id(''tempdb..#ReportData'') is not null drop table #ReportData Create table #ReportData ( LedSubNumber Int NULL,MainType Char(1) NULL, BranchCode Char(3) Null, Type Char(2) Null, ReferenceNumber Char(25) Null, ReferenceDate DateTime Null, DueDate DateTime Null, ReferenceAmount float null, BalanceAmount float null, CurrencyCode Int Null,ExchangeRate float Null, AcCode char(8) Null, AccountNumber Int Null,SalesPersonCode Int Null, SalespersonName char(40) Null,POChequeNumber char(120) Null, POChequeDate DateTime Null, Clear float null,HoldAmount float null, Remarks char(50) null,AccountName char(40) Null, AccountID char(15) Null, InterestRate float Null,AccountEmail char(50) Null, AccountPhone char(50) Null, AccountFax char(50) Null,AccountAddress1 char(50) Null, AccountAddress2 Char(50) Null, AccountAddress3 char(50) Null,AccountCity char(40) Null, AccountPin char(40) Null, GraceDate DateTime Null, RepDays Int Null, InvDays Int Null,DueDays Int Null, CrDays Int Null, CityCode Int Null, CityName char(40) null, AreaCode Int Null, AreaName char(40) null, CurrencyName char(40) Null, AccountGroupCode Int Null, AccountGroupName char(40) Null, Field1 char(30) Null, Field2 char(25) null, Location char(25) null, FinancialYearCode char(2) null, AccountCode char(8) null, CustomerGroupName char(40) null, CustomerGroupCode Int Null ) Insert into #ReportData select FOut.LedSubNumber,"A" as MainType,fOut.BranchCode,fOut.Type,fOut.ReferenceNumber,fOut.ReferenceDate, (fOut.DueDate) as DueDate,fOut.ReferenceAmount, fOut.BalanceAmount,fOut.CurrencyCode, ( case when isnull( fOut.ExchangeRate, 0 ) = 0 then 1 else fout.exchangerate end ) as ExchangeRate,fOut.AccountCode as AcCode ,fOut.AccountNumber, fOut.SalesPersonCode,Sper.SalespersonName,fOut.POChequeNumber,fOut.POChequeDate,Sum( FClr.Cleared ) Clear, fOut.HoldAmount,fOut.Remarks,Acm.AccountName,Acm.AccountID,Acm.InterestRate, Acm.AccountEmail, Acm.AccountPhone, Acm.AccountFax, Acm.AccountAddress1, Acm.AccountAddress2, Acm.AccountAddress3, Acm.AccountCity, Acm.AccountPin, (Fout.DueDate ) as GraceDate,DateDiff(Day,Fout.DueDate,''@end_date'') as RepDays , DateDiff(day, FOut.ReferenceDate, ''@end_date'') as InvDays , DateDiff(day, FOut.DueDate, ''@end_date'') as DueDays , DateDiff(day, FOut.ReferenceDate, FOut.DueDate ) CrDays , 0 CityCode, '' CityName ,0 AreaCode, '' AreaName ,'' CurrencyName ,0 AccountGroupCode, '' AccountGroupName , fOut.Field1, fOut.Field2,SA.Location , Acm.FinancialYearCode, Acm.AccountCode , 0 CustomerGroupName, '' CustomerGroupCode From foutstanding fOut (nolock) Inner Join FClearingView FCLr (nolock) On fOut.FinancialYearCode=fClr.ClearingYear And fOut.ProcessingPointCode=fClr.ClearingPPCode And fOut.LedSerialNumber=fClr.ClearingSrNo And fOut.LedSubNumber=fClr.ClearingSubNo Inner Join Account ACM (nolock) On fOut.Financialyearcode=Acm.Financialyearcode And fOut.AccountCode=Acm.AccountCode Left Outer Join SubAccount SA (nolock) On fOut.Financialyearcode=SA.Financialyearcode And fOut.AccountCode=SA.AccountCode And FOut.AccountNumber = SA.Number Left Outer Join Sperson Sper (nolock) On fOut.SalespersonCode = Sper.SalespersonCode Inner Join AcInfo AcI (nolock) On AcI.FinancialYearCode = Acm.FinancialYearCode and Acm.AccountCode = AcI.AccountCode where fClr.ClearedDate <= ''@end_date'' and fOut.Type IN(''R'',''AP'',''DN'',''JD'') and DateDiff(day , fOut.referenceDate , ''@end_date'' ) >= 0 and ( Acm.GroupCompany is not null ) and Acm.AccountCode in (''C010201'',''C012401'',''C0000018'') Group By FOut.LedSubNumber,fOut.BranchCode, fOut.Type,fOut.ReferenceNumber,fOut.ReferenceDate,fOut.DueDate,Aci.GraceDays,fOut.ReferenceAmount, fOut.BalanceAmount,fOut.CurrencyCode,fOut.ExchangeRate,fOut.AccountCode,fOut.AccountNumber, fOut.SalesPersonCode,SPer.SalespersonName,fOut.POChequeNumber,fOut.POChequeDate,Acm.AccountName, Acm.AccountID,Acm.InterestRate, Acm.AccountEmail, Acm.AccountPhone, Acm.AccountFax, Acm.AccountAddress1, Acm.AccountAddress2, Acm.AccountAddress3, Acm.AccountCity, Acm.AccountPin, Aci.GraceDays, fOut.HoldAmount,fOut.Remarks , fOut.Field1, fOut.Field2, SA.Location, Acm.FinancialYearCode, Acm.AccountCode Having abs(fOut.BalanceAmount - Sum( fClr.Cleared )) >= 0.01 Insert into #ReportData select FOut.LedSubNumber,''B'' as MainType,fOut.BranchCode,fOut.Type,fOut.ReferenceNumber,fOut.ReferenceDate,(fOut.DueDate) as DueDate, ( 0 - fOut.ReferenceAmount) as ReferenceAmount , ( 0 - fOut.BalanceAmount ) as BalanceAmount,fOut.CurrencyCode,case when isnull( fOut.ExchangeRate, 0 ) = 0 then 1 else fout.exchangerate end as ExchangeRate,fOut.AccountCode as AcCode,fOut.AccountNumber, fOut.SalesPersonCode,SPer.SalespersonName,fOut.POChequeNumber,fOut.POChequeDate,Sum( 0 - FClr.Cleared ) Clear , 0 as HoldAmount,'' '' as Remarks,Acm.AccountName,Acm.AccountID,Acm.InterestRate, Acm.AccountEmail, Acm.AccountPhone, Acm.AccountFax, Acm.AccountAddress1, Acm.AccountAddress2, Acm.AccountAddress3, Acm.AccountCity, Acm.AccountPin, (Fout.DueDate) as GraceDate, 0 as RepDays , 0 as InvDays , 0 as DueDays , DateDiff(day, FOut.ReferenceDate, FOut.DueDate) CrDays , 0 CityCode, '' CityName ,0 AreaCode, '' AreaName ,'' CurrencyName ,0 AccountGroupCode, '' AccountGroupName , fOut.Field1, fOut.Field2,SA.Location , Acm.FinancialYearCode, Acm.AccountCode , 0 CustomerGroupName, '' CustomerGroupCode From foutstanding fOut (nolock) Inner Join FClearingView FCLr (nolock) On fOut.FinancialYearCode=fClr.ClearingYear And fOut.ProcessingPointCode=fClr.ClearingPPCode And fOut.LedSerialNumber=fClr.ClearingSrNo And fOut.LedSubNumber=fClr.ClearingSubNo Inner Join Account ACM (nolock) On fOut.Financialyearcode=Acm.Financialyearcode And fOut.AccountCode = Acm.AccountCode Left Outer Join SubAccount SA (nolock) On fOut.Financialyearcode=SA.Financialyearcode And fOut.AccountCode = SA.AccountCode and FOut.AccountNumber = SA.Number Left Outer Join SPerson SPer (nolock) On fOut.SalespersonCode = Sper.SalespersonCode Inner Join AcInfo AcI (nolock) on AcI.FinancialYearCode = Acm.FinancialYearCode and Acm.AccountCode = AcI.AccountCode Where fClr.ClearedDate <= ''@end_date'' and fOut.Type IN(''P'',''AR'',''CN'',''JC'') and fOut.referenceDate <= ''''@end_date'''' and ( Acm.GroupCompany is not null ) and Acm.AccountCode in ( ''C010201'', ''C012401'', ''C0000018'' ) Group By FOut.LedSubNumber,fOut.BranchCode, fOut.Type,fOut.ReferenceNumber,fOut.ReferenceDate,fOut.DueDate,Aci.GraceDays,fOut.ReferenceAmount, fOut.BalanceAmount,fOut.CurrencyCode,fOut.ExchangeRate,fOut.AccountCode,fOut.AccountNumber, fOut.SalesPersonCode,Sper.SalespersonName,fOut.POChequeNumber,fOut.POChequeDate,Acm.AccountName, Acm.AccountID,Acm.InterestRate, Acm.AccountEmail, Acm.AccountPhone, Acm.AccountFax, Acm.AccountAddress1, Acm.AccountAddress2, Acm.AccountAddress3, Acm.AccountCity, Acm.AccountPin, Aci.GraceDays , fOut.Field1, fOut.Field2, SA.Location, Acm.FinancialYearCode, Acm.AccountCode Having abs(fOut.BalanceAmount - Sum( fClr.Cleared )) >= 0.01 Insert into #ReportData select 0,''B'' as MainType, ''001'' as BranchCode, ''PD'' as Type, PDC.ReferenceNumber, PDC.ChequeDate as ReferenceDate, PDC.ChequeDate as DueDate, (0 - PDC.Amount) as ReferenceAmount, (0 - PDC.Amount) as BalanceAmount, PDC.CurrencyCode, case when isnull( PDC.ExchangeRate, 0 ) = 0 then 1 else PDC.exchangerate end as ExchangeRate, PDC.AccountCode as AcCode, '' as AccountNumber, '' as SalesPersonCode, '' SalespersonName, PDC.ChequeNo POChequeNo, PDC.ChequeDate POChequeDate, 0 as Clear, 0 as HoldAmount, '' as Remarks, Acm.AccountName, Acm.AccountID, 0 as InterestRate, Acm.AccountEmail, Acm.AccountPhone, Acm.AccountFax, Acm.AccountAddress1, Acm.AccountAddress2, Acm.AccountAddress3, Acm.AccountCity, Acm.AccountPin, 0 as GraceDate, 0 as RepDays , 0 as InvDays, 0 as DueDays, 0 as CrDays , 0 CityCode, '''' CityName ,0 AreaCode, '''' AreaName ,'''' CurrencyName ,0 AccountGroupCode, '''' AccountGroupName , '''' as Field1, '''' as Field2, SA.Location , Acm.FinancialYearCode, Acm.AccountCode , 0 CustomerGroupName, '''' CustomerGroupCode from PostDatedCheque PDC (nolock) Inner Join Account ACM (nolock) On PDC.AccountCode = Acm.AccountCode and Acm.FinancialYearCode = ''06'' Inner Join AcInfo AcI (nolock) On AcI.FinancialYearCode = Acm.FinancialYearCode and Acm.AccountCode = AcI.AccountCode Left Outer Join SubAccount SA (nolock) On ACM.Financialyearcode=SA.Financialyearcode And PDC.AccountCode = SA.AccountCode and SA.Number = 0 Where 1=1 and PDC.VoucherType in(''R'') and ( Acm.GroupCompany is not null ) and Acm.AccountCode in ( ''C010201'', ''C012401'', ''C0000018'' ) select a.despatchnumber,a.refdate,c.itemname,c.itemdescription,c.itemcategorycode,b.salequantity,b.itemamount into #2 from salematdespatchmas a,salematdespatchdet b,item c where a.moduleid=b.moduleid and a.branchcode=b.branchcode and a.processingpointcode=b.processingpointcode and a.financialyearcode=b.financialyearcode and a.serialnumber=b.serialnumber and b.itemcode=c.itemcode select a.accountname,a.referencenumber,a.referencedate,a.duedate,a.duedays,a.referenceamount, a.balanceamount-a.clear as actualbal, b.itemname,b.itemdescription,b.salequantity,b.itemamount,b.itemcategorycode INTO #C from #reportdata a,#2 b where a.referencenumber=b.despatchnumber SELECT B.ITEMCATEGORYNAME, A.* FROM #C A,FMGENERAL.DBO.ITEMCATEGORY B WHERE A.ITEMCATEGORYCODE=B.ITEMCATEGORYCODE ORDER BY A.REFERENCENUMBER' END EXEC (@SQLSTR) -- --To execute SP_CategoryOutstanding '20160620','20160620'
stored procedure
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered
Sorry, I should have read your code more carefully. I thought it was all in a string variable because you had scripted it out from SSMS but I see now it's dynamic SQL. In that case there are two ways to fix this. Firstly, don't do it as dynamic SQL. Just put that SQL in as the body of the proc. It's a lot of code but I can't see anything obvious that means it has to be dynamic SQL. Just replace the pairs of single quotes ('') with one single quote. If you want it as dynamic SQL you need to include variables like this: ...and fOut.referenceDate
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dear David..Thanks for reply..resolved..
0 Likes 0 ·
Yes quotes were the issue. Thanks again.
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
@end_date is wrapped in quotes almost every time it's mentioned. Those quotes should all be removed because it's a parameter name not a string.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dear David..thanks for reply. Tried removing the quotes still the error message is prompted; while executing the sp. SP_CategoryOutstanding '20160620','20160620' Msg 137, Level 15, State 2, Line 29 Must declare the scalar variable "@END_DATE". Thanks in advance.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.