SQL: Msg 156, Level 15, State 1 :Incorrect syntax near the keyword 'END'

Question

Ok i have 2 questions 1) I've googled that error(in title) but I cant seem to get it to help me, my proc:

CREATE PROCEDURE [dbo].[p_Target]
AS
BEGIN
SET NOCOUNT ON

    CREATE TABLE #tmp1 
    (
        AUD_ID BIGINT,
        RowCounter BIGINT,
        DistinctCounter BIGINT,
        NACounter BIGINT,
        Total BIGINT,
        [Status] VARCHAR(MAX)
    )
   INSERT INTO #tmp1 EXEC [p_GetCompleteIncompleteNaOverviewSCORE] 
DECLARE @Total AS INT

    SELECT @Total = COUNT(*)
    FROM (
    SELECT CASE WHEN CONVERT(DATETIME,CONVERT(CHAR(10),DATEADD(DAY,0,t2.AUD_TargetDate), 101)) < CONVERT(DATETIME,CONVERT(CHAR(10),DATEADD(DAY,0,GETDATE()), 101))
            THEN 'Over Due: '
            ELSE 'On Time: ' END AS [Target Status]
            FROM #tmp1 t1 INNER JOIN dbo.Audit t2
            ON t1.AUD_ID = t2.AUD_ID
            WHERE t1.[Status] = 'Open') DER 

    SELECT ([Target Status] + '' + CAST(COUNT(*) AS NVARCHAR(255))  + ' of ' + CAST(@Total AS NVARCHAR(255))) AS TargetStatus, (SELECT COUNT(*)) * 100 / (SELECT ([Target Status] + '' + CAST(COUNT(*) AS NVARCHAR(255))) AS [Count]
    FROM (
    SELECT CASE WHEN CONVERT(DATETIME,CONVERT(CHAR(10),DATEADD(DAY,0,t2.AUD_TargetDate), 101)) < CONVERT(DATETIME,CONVERT(CHAR(10),DATEADD(DAY,0,GETDATE()), 101))
            THEN 'Over Due: '
            ELSE 'On Time: ' END AS [Target Status]
            FROM #tmp1 t1 INNER JOIN dbo.Audit t2
            ON t1.AUD_ID = t2.AUD_ID
            WHERE t1.[Status] = 'Open') DER
    GROUP BY [Target Status] 
    END
GO

I've tied adding ) to NVARCHAR(255))) AS [Count] but still errors,

2)I need to get AS [Count] as a percentag, if anyone can help? That is my try code maybe i am on the right track.?

Answer 1

You're missing a close paren on your final subselect. Also, you're missing the 'S' in SET NOCOUNT. Updates below.

CREATE PROCEDURE [dbo].[p_Target]
AS
BEGIN
SET NOCOUNT ON

    CREATE TABLE #tmp1 
    (
        AUD_ID BIGINT,
        RowCounter BIGINT,
        DistinctCounter BIGINT,
        NACounter BIGINT,
        Total BIGINT,
        [Status] VARCHAR(MAX)
    )
   INSERT INTO #tmp1 EXEC [p_GetCompleteIncompleteNaOverviewSCORE] 
DECLARE @Total AS INT

    SELECT @Total = COUNT(*)
    FROM (
    SELECT CASE WHEN CONVERT(DATETIME,CONVERT(CHAR(10),DATEADD(DAY,0,t2.AUD_TargetDate), 101)) < CONVERT(DATETIME,CONVERT(CHAR(10),DATEADD(DAY,0,GETDATE()), 101))
            THEN 'Over Due: '
            ELSE 'On Time: ' END AS [Target Status]
            FROM #tmp1 t1 INNER JOIN dbo.Audit t2
            ON t1.AUD_ID = t2.AUD_ID
            WHERE t1.[Status] = 'Open') DER 

    SELECT ([Target Status] + '' + CAST(COUNT(*) AS NVARCHAR(255))  + ' of ' + CAST(@Total AS NVARCHAR(255))) AS TargetStatus, (SELECT COUNT(*)) * 100 / (SELECT ([Target Status] + '' + CAST(COUNT(*) AS NVARCHAR(255))) AS [Count]
    FROM (
    SELECT CASE WHEN CONVERT(DATETIME,CONVERT(CHAR(10),DATEADD(DAY,0,t2.AUD_TargetDate), 101)) < CONVERT(DATETIME,CONVERT(CHAR(10),DATEADD(DAY,0,GETDATE()), 101))
            THEN 'Over Due: '
            ELSE 'On Time: ' END AS [Target Status]
            FROM #tmp1 t1 INNER JOIN dbo.Audit t2
            ON t1.AUD_ID = t2.AUD_ID
            WHERE t1.[Status] = 'Open') DER
    GROUP BY [Target Status] 
    )
    END
GO