CODEWITHSUNDEEP
sql-servert-sql
Ruan
Ruan

Reputation: 127

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

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.?

Upvotes: 0

Views: 136619

Answers (4)

Seng Phally
Seng Phally

Reputation: 1

Create Table TblAccount (UserName nvarchar(20) Primary Key,UserPwd varchar(20) not null)

Declare @i int
Set @i=1

While @i<=10
I have one question 
BEGIN
    Insert INTO TblAccount Values('Seng Vitou'+CAST(@i AS VARCHar(2)),REVERSE('pwd'+CAST(@i as varchar(2))

    SET @i=@i+1

End   

Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'SET'.

Upvotes: 0

Chris Satola
Chris Satola

Reputation: 284

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

Upvotes: 5

STW
STW

Reputation: 46366

It looks like your SELECT statement above your last last FROM statement has an un-closed parentheses. That would make the parser throw an error when it hits the END statement, since it still thinks it's working on the SELECT.

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]
       ^

Whenever you get an incorrect Syntax error it's best to carefully go through your code and check that all statements are well formed and complete. Usually google can't help you, since it's very specific to your code. A good approach to finding the fault is to locate the point of the error (END in this case) and work backwards. The cause of the error may be quite far above the actual point that the error is thrown, but it will be somewhere above the error. And reading backwards can help you catch simple mistakes.

Upvotes: 0

Mike
Mike

Reputation: 2559

Is it the missing 'S' on SET NOCOUNT ON after the initial begin perhaps?

Upvotes: 0

Related Questions