-
SP Error
Hi pls Help
I have the following Sp which I'm trying to manipulate
and I keep getting the following error's
Code:
Server: Msg 170, Level 15, State 1, Procedure Tonyg_ReportChart_GetBDOnlineMonthlyCountsNew1, Line 52
Line 52: Incorrect syntax near '2'.
Server: Msg 156, Level 15, State 1, Procedure Tonyg_ReportChart_GetBDOnlineMonthlyCountsNew1, Line 60
Incorrect syntax near the keyword 'inner'.
my Sp is as follows
Code:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER procedure dbo.Tonyg_ReportChart_GetBDOnlineMonthlyCountsNew1
@ParamBusinessClassID int,
@UserID int
--@ReportType Varchar(50)
as
declare
@ProductID_int int
IF OBJECT_ID('tempdb..drop table #TransactionRolesTable') IS NOT NULL
begin
drop table #TransactionRolesTable
end
IF OBJECT_ID('tempdb..drop table #Rpt_TransactionTable') IS NOT NULL
begin
drop table #TransactionRolesTable
end
/*
create table that holds all transaction for the date period
*/
CREATE TABLE #TransactionRolesTable (
ID_int int primary key IDENTITY(1,1),
RoleID_int INT,
ProductID_int int
)
/*
add all Product roles for this user within this business class
*/
insert into #TransactionRolesTable (RoleID_int, ProductID_int)
select distinct(admin_UserRole_int), ProductID_int from tonyg_Schemeshop_Products
Join Userroles on admin_UserRole_int = roleid
where BusinessClassID_int = @ParamBusinessClassID
and userid = @userid
/*
Report type could be
MonthlyCounts
MonthlyGWP
YTDProductBreakDownCount
*/
/* Tran Table for Year
*/
SELECT min(distinct QuoteLogID_int), ProductID_int, QuoteID_int, Logged_DateTime AS Report_date, TransactionType_varchar, Policystartdate_date,
BusinessClassID_int, BrokerCommission_money, BrokerDirectCommission_money, GrossPremium_money, Status_varchar, productID_int into rpt_transactiontable
fROM Tonyg_SchemeShop_LogQuotes WHERE (DATEPart(YYYY, Logged_datetime) = DATEPart(YYYY, GetDate())) and BusinessClassID_int = @ParamBusinessClassID and
(Tonyg_SchemeShop_LogQuotes.ProductID_int in (select ProductID_int from #TransactionRolesTable))
GROUP BY ProductID_int, QuoteID_int, Logged_DateTime, TransactionType_varchar, BusinessClassID_int, round(BrokerCommission_money 2,2),
BrokerDirectCommission_money, GrossPremium_money, Status_varchar, QuoteLogID_int HAVING (TransactionType_varchar = 'Quote') OR (TransactionType_varchar = 'referral') OR
(TransactionType_varchar = 'NB') ORDER BY QuoteLogID_int
/*Reports Table */
select * from rpt_transactiontable where TransactionType_varchar = 'NB'
update rpt_transactiontable inner join tab_qab_logquotes on rpt_transactiontable.QuoteLogID_int = tab_qab_logquotes.QuoteLogID_int
set rpt_transactiontable.report_date = tab_qab_logquotes.PolicyStartDate_date
where rpt_transactiontable.QuoteLogID_int = tab_qab_logquotes.QuoteLogID_int
--UPDATE rpt_transactiontable INNER JOIN tab_QAB_LogQuotes ON rpt_transactiontable.FirstOfQuoteLogID_int = tab_QAB_LogQuotes.QuoteLogID_int SET rpt_transactiontable.Report_date = [tab_qab_logquotes!polcicystartdate_datetime]
--WHERE (((rpt_transactiontable.Tpye)="NB"));
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Ta for looking at my problem
-
Re: SP Error
try below SP
Code:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER procedure dbo.Tonyg_ReportChart_GetBDOnlineMonthlyCountsNew1
@ParamBusinessClassID int,
@UserID int
--@ReportType Varchar(50)
as
declare
@ProductID_int int
IF OBJECT_ID('tempdb..drop table #TransactionRolesTable') IS NOT NULL
begin
drop table #TransactionRolesTable
end
IF OBJECT_ID('tempdb..drop table #Rpt_TransactionTable') IS NOT NULL
begin
drop table #TransactionRolesTable
end
/*
create table that holds all transaction for the date period
*/
CREATE TABLE #TransactionRolesTable (
ID_int int primary key IDENTITY(1,1),
RoleID_int INT,
ProductID_int int
)
/*
add all Product roles for this user within this business class
*/
insert into #TransactionRolesTable (RoleID_int, ProductID_int)
select distinct(admin_UserRole_int), ProductID_int from tonyg_Schemeshop_Products
Join Userroles on admin_UserRole_int = roleid
where BusinessClassID_int = @ParamBusinessClassID
and userid = @userid
/*
Report type could be
MonthlyCounts
MonthlyGWP
YTDProductBreakDownCount
*/
/* Tran Table for Year
*/
SELECT min(distinct QuoteLogID_int), ProductID_int, QuoteID_int, Logged_DateTime AS Report_date, TransactionType_varchar, Policystartdate_date,
BusinessClassID_int, BrokerCommission_money, BrokerDirectCommission_money, GrossPremium_money, Status_varchar, productID_int into rpt_transactiontable
fROM Tonyg_SchemeShop_LogQuotes
WHERE (DATEPart(YYYY, Logged_datetime) = DATEPart(YYYY, GetDate())) and BusinessClassID_int = @ParamBusinessClassID and
(Tonyg_SchemeShop_LogQuotes.ProductID_int in (select ProductID_int from #TransactionRolesTable))
GROUP BY ProductID_int, QuoteID_int, Logged_DateTime, TransactionType_varchar, BusinessClassID_int, round(BrokerCommission_money, 2,2),
BrokerDirectCommission_money, GrossPremium_money, Status_varchar, QuoteLogID_int
HAVING (TransactionType_varchar = 'Quote') OR (TransactionType_varchar = 'referral') OR
(TransactionType_varchar = 'NB') ORDER BY QuoteLogID_int
/*Reports Table */
select * from rpt_transactiontable where TransactionType_varchar = 'NB'
update rpt_transactiontable
set rpt_transactiontable.report_date = tab_qab_logquotes.PolicyStartDate_date
FROM rpt_transactiontable
inner join tab_qab_logquotes on rpt_transactiontable.QuoteLogID_int = tab_qab_logquotes.QuoteLogID_int
where rpt_transactiontable.QuoteLogID_int = tab_qab_logquotes.QuoteLogID_int
--UPDATE rpt_transactiontable INNER JOIN tab_QAB_LogQuotes ON rpt_transactiontable.FirstOfQuoteLogID_int = tab_QAB_LogQuotes.QuoteLogID_int SET rpt_transactiontable.Report_date = [tab_qab_logquotes!polcicystartdate_datetime]
--WHERE (((rpt_transactiontable.Tpye)="NB"));
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
you was missing comma (,) in round(BrokerCommission_money, 2,2)
-
Re: SP Error
Ta very much for you help and tim e!! it works :o :o
-
Re: SP Error
