ALTER PROCEDURE won't compile - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

ALTER PROCEDURE won't compile

Author

Topic

dbaRobin
Starting Member

3 Posts

Posted - 2013-09-04 : 10:55:17

The following query runs fine and dandy, although slow for my taste:

-- Add the parameters for the stored procedure here
DECLARE @StartDate date = '1/1/2013'
,@EndDate date = '12/31/2013'
,@Policy varchar(16) = NULL
,@ProcessorID varchar(max) = 'Diana Nguyen,Meriah Taylor'

-- Split the list of passed in users names into a table set and insert into the local variable table
-- joining on the Bonita journal database for the IDs
;WITH cteProcessors AS(
SELECT bu.[USER_USERNAME_] AS UserId, VALUE AS Name
FROM [WorkflowReporting].[dbo].[fn_SplitListToTable](@ProcessorID, ',')
INNER JOIN [annuity_csp_journal].[dbo].[BN_USER] bu
ON (bu.[USER_FIRST_NAME_] + ' ' + bu.[USER_LAST_NAME_] = VALUE)
)
,cteCounts AS(
SELECT
si.UpdatedBy
,pr.RequestTypeId
,pr.RequestStatus
,pr.PolicyNumber
,COUNT(CASE WHEN si.AttributeValue = 'Processed' THEN si.ProcessInstanceId END) Processed
,COUNT(CASE WHEN si.AttributeValue = 'Pending' THEN si.ProcessInstanceId END) Pending
,COUNT(CASE WHEN si.AttributeValue IN ('Denial','Deniel') THEN si.ProcessInstanceId END) Denial
,COUNT(CASE WHEN si.AttributeValue = 'CallOut' THEN si.ProcessInstanceId END) SentToCallOut
,COUNT(CASE WHEN si.AttributeValue = 'Over 250K' THEN si.ProcessInstanceId END) SentToManager
,COUNT(CASE WHEN si.AttributeValue = 'Rescan' THEN si.ProcessInstanceId END) SentToImaging
,COUNT(CASE WHEN si.AttributeName = 'STARTDATE' AND si.AttributeValue <> si.OldAttributeValue THEN si.ProcessInstanceId END) Scheduled
,COUNT(CASE WHEN si.AttributeValue = 'Dispatch' THEN si.ProcessInstanceId END) Rerouted
FROM
[csp_data].[dbo].[ProcessInstance] pr
INNER JOIN [csp_data].[dbo].[StepInstance] si ON (pr.Id = si.ProcessInstanceId)
WHERE
pr.UpdatedOn BETWEEN @StartDate AND @EndDate
AND (pr.PolicyNumber = @Policy OR @Policy IS NULL)
GROUP BY
si.UpdatedBy
,pr.RequestTypeId
,pr.RequestStatus
,pr.PolicyNumber
)
-- Get additional counts from the ProcessInstance table joining to the CTE
SELECT
p.Name as 'Processor'
,rt.[Description] as RequestType
,c.PolicyNumber
,c.RequestStatus
,COUNT(*) 'ProcessorCount'
,COUNT(CASE WHEN pr.CreatedBy = p.UserId THEN 1 END) Created
,c.Processed
,c.Pending
,c.Denial
,c.SentToCallOut
,c.SentToManager
,c.SentToImaging
,c.Scheduled
,c.Rerouted
,COUNT(CASE WHEN pr.SupporttechId IS NOT NULL THEN 1 END) SupportTech
,SUM(pr.QCErrorCount) ErrorCount
--,ROUND(CAST((COUNT(*)-Sum(pr.QCErrorCount)) AS float)/CAST(COUNT(*) AS float),2) AS 'QualityPercent'
FROM
cteCounts c
INNER JOIN [csp_data].[dbo].[ProcessInstance] pr ON
c.PolicyNumber = pr.PolicyNumber
AND c.RequestStatus = pr.RequestStatus
AND c.RequestTypeId = pr.RequestTypeId
AND c.UpdatedBy = pr.ProcessorId
INNER JOIN csp_data.dbo.RequestType rt ON
c.RequestTypeId = rt.Id
INNER JOIN cteProcessors p ON
c.UpdatedBy = p.UserId
GROUP BY
p.Name
,rt.[Description]
,c.PolicyNumber
,c.RequestStatus
,c.Processed
,c.Pending
,c.Denial
,c.SentToCallOut
,c.SentToManager
,c.SentToImaging
,c.Scheduled
,c.Rerouted
ORDER BY
Processor
,RequestType
,RequestStatus
,PolicyNumber

When I drop that query into an ALTER PROCEDURE statement, it won't run. I get:

Msg 102, Level 15, State 1, Procedure prc_rpt_UserDashboard, Line 102
Incorrect syntax near 'PolicyNumber'.

Here's the final code I'm trying to run to change the stored proc:

USE [WorkflowReporting]
GO
/****** Object: StoredProcedure [dbo].[prc_rpt_UserDashboard] Script Date: 08/20/2013 13:31:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
Project: 7436
Date: 04/01/2013
Description: This report feeds data to a SSRS report. Get monthly data by request type.
*/

ALTER PROCEDURE [dbo].[prc_rpt_UserDashboard]
-- Add the parameters for the stored procedure here
@StartDate date
,@EndDate date
,@Policy varchar(16)
,@ProcessorID varchar(max)

AS
BEGIN

--Per Anders Pedersen
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

-- Create a list of passed in user names
-- joining on the Bonita journal database for the IDs
;WITH cteProcessors AS(
SELECT bu.[USER_USERNAME_] AS UserId, VALUE AS Name
FROM [WorkflowReporting].[dbo].[fn_SplitListToTable](@ProcessorID, ',')
INNER JOIN [annuity_csp_journal].[dbo].[BN_USER] bu
ON (bu.[USER_FIRST_NAME_] + ' ' + bu.[USER_LAST_NAME_] = VALUE)
)
,cteCounts AS(
SELECT
si.UpdatedBy
,pr.RequestTypeId
,pr.RequestStatus
,pr.PolicyNumber
,COUNT(CASE WHEN si.AttributeValue = 'Processed' THEN si.ProcessInstanceId END) Processed
,COUNT(CASE WHEN si.AttributeValue = 'Pending' THEN si.ProcessInstanceId END) Pending
,COUNT(CASE WHEN si.AttributeValue IN ('Denial','Deniel') THEN si.ProcessInstanceId END) Denial
,COUNT(CASE WHEN si.AttributeValue = 'CallOut' THEN si.ProcessInstanceId END) SentToCallOut
,COUNT(CASE WHEN si.AttributeValue = 'Over 250K' THEN si.ProcessInstanceId END) SentToManager
,COUNT(CASE WHEN si.AttributeValue = 'Rescan' THEN si.ProcessInstanceId END) SentToImaging
,COUNT(CASE WHEN si.AttributeName = 'STARTDATE' AND si.AttributeValue <> si.OldAttributeValue THEN si.ProcessInstanceId END) Scheduled
,COUNT(CASE WHEN si.AttributeValue = 'Dispatch' THEN si.ProcessInstanceId END) Rerouted
FROM
[csp_data].[dbo].[ProcessInstance] pr
INNER JOIN [csp_data].[dbo].[StepInstance] si ON (pr.Id = si.ProcessInstanceId)
WHERE
pr.UpdatedOn BETWEEN @StartDate AND @EndDate
AND (pr.PolicyNumber = @Policy OR @Policy IS NULL)
GROUP BY
si.UpdatedBy
,pr.RequestTypeId
,pr.RequestStatus
,pr.PolicyNumber
)
-- Get additional counts from the ProcessInstance table joining to the CTE
SELECT
p.Name as 'Processor'
,rt.[Description] as RequestType
,c.PolicyNumber
,c.RequestStatus
,COUNT(*) 'ProcessorCount'
,COUNT(CASE WHEN pr.CreatedBy = p.UserId THEN 1 END) Created
,c.Processed
,c.Pending
,c.Denial
,c.SentToCallOut
,c.SentToManager
,c.SentToImaging
,c.Scheduled
,c.Rerouted
,COUNT(CASE WHEN pr.SupporttechId IS NOT NULL THEN 1 END) SupportTech
,SUM(pr.QCErrorCount) ErrorCount
--,ROUND(CAST((COUNT(*)-Sum(pr.QCErrorCount)) AS float)/CAST(COUNT(*) AS float),2) AS 'QualityPercent'
FROM
cteCounts c
INNER JOIN [csp_data].[dbo].[ProcessInstance] pr ON
c.PolicyNumber = pr.PolicyNumber
AND c.RequestStatus = pr.RequestStatus
AND c.RequestTypeId = pr.RequestTypeId
AND c.UpdatedBy = pr.ProcessorId
INNER JOIN csp_data.dbo.RequestType rt ON
c.RequestTypeId = rt.Id
INNER JOIN cteProcessors p ON
c.UpdatedBy = p.UserId
GROUP BY
p.Name
,rt.[Description]
,c.PolicyNumber
,c.RequestStatus
,c.Processed
,c.Pending
,c.Denial
,c.SentToCallOut
,c.SentToManager
,c.SentToImaging
,c.Scheduled
,c.Rerouted
ORDER BY
Processor
,RequestType
,RequestStatus
,PolicyNumber