I need to clear the revenue values from the Stage table from all records and then update the same from the working table. I am trying to do an INSERT and UPDATE in one stored procedure in SQL Server 2008. When I execute the UPDATE query and the INSERT query separately, they are working fine. However, when I try to include them in one stored procedure, I am getting a syntax error.
My stored procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF object_ID(N'[dbo].[usp_AtRiskAsset_SFLoadStaging]') IS NOT NULL
DROP PROCEDURE [dbo ].[usp_AtRiskAsset_SFLoadStaging]
GO
CREATE PROCEDURE [dbo].[usp_AtRiskAsset_SFLoadStaging]
AS
BEGIN
INSERT INTO dbo.ARA_Stage (Id, [CanceledRevenue], [YearToDateRevenue], [PriorYearRevenue])
SELECT
ARA.[Id],
'NULL',
'NULL',
'NULL'
FROM
dbo.ARA ARA
INNER JOIN
dbo.Asset AST ON AST.[Id] = ARA.Asset__c
INNER JOIN
dbo.[Case] CS ON CS.[Id] = ARA.Case__c
WHERE
CS.IsClosed = 'false'
AND CS.Record_Type_Name__c = 'At Risk'
AND AST.IsActive__c = 'true'
UPDATE dbo.ARA_Stage
SELECT
ARS.Id,
ARW.CanceledRevenue,
ARW.YearToDateRevenue,
ARW.PriorYearRevenue
FROM
dbo.ARA_Working ARW
INNER JOIN
dbo.Account AC ON ARW.AccountNumber = AC.Contract_Number__c
INNER JOIN
dbo.Asset AST ON ARW.PDCT01_ID = AST.PDCT01_Id__c
AND AST.AccountId = AC.Id
INNER JOIN
dbo.ARA ARS ON AST.Id = ARS.Asset__C
INNER JOIN
dbo.Product2 PRD ON ARW.PDCT01_ID = PRD.PDCT01_Id__c
AND AST.PDCT01_Id__c = ARW.PDCT01_ID
INNER JOIN
[dbo].[Case] CS ON ARS.Case__c = CS.Id
WHERE
CS.Account_Contract_Number__c = ARW.AccountNumber
AND CS.Record_Type_Name__c = 'At Risk'
AND CS.IsClosed = 'false'
END
GO
The error:
Msg 156, Level 15, State 1, Procedure usp_AtRiskAsset_SFLoadStaging, Line 46
Incorrect syntax near the keyword 'SELECT'.
