SP Error

[holly]

Hi pls Help
I have the following Sp which I'm trying to manipulate
and I keep getting the following error's

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

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

[riteshjain1982]

try below SP

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)

[holly]

Ta very much for you help and tim e!! it works

[riteshjain1982]

you are welcome