CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Tarek Orfali
Tarek Orfali

Reputation: 73

Having some trouble creating a stored procedure (Incorrect syntax near 'end')

Update question

Changed the stored procedure accordingly, but it's not returning any values for some reason, even though I added RETURN @UserID right above the last END.

Update 2

Alright never mind, it's just Entity Framework that's teasing me. I just tried running the stored procedure from SSMS and it worked fine :)

I've been copying some of my other stored procedures, but every time my requirements for them change, and so does the content of them. Not sure what I did wrong this time, could anyone please point me to the right direction?

CREATE PROCEDURE [dbo].[GetUserID]
(   
    -- Add the parameters for the function here
    @UUID varchar(36),
    @SuperID varchar(33)
)
AS
BEGIN
    SET NOCOUNT ON
    SET ARITHABORT ON

    DECLARE @SuperIDExists bit
    DECLARE @UserID int

    SELECT @SuperIDExists = CASE 
                               WHEN EXISTS (SELECT *
                                            FROM dbo.[Users] AS u
                                            WHERE u.SuperID = @SuperID)
                                  THEN CAST(1 as bit)
                                  ELSE CAST(0 as bit)
                            END

    IF @SuperIDExists = 1
    BEGIN
        SELECT @UserID = (SELECT u.ID
                          FROM dbo.[Users] AS u
                          WHERE u.SuperID = @SuperID)
    END
    ELSE
    BEGIN
        SELECT @UserID = (SELECT u.ID
                          FROM dbo.[Users] as U
                          WHERE u.UUID = @UUID)
    END

The error I get is:

Msg 102, Level 15, State 1, Procedure GetUserID, Line 45 [Batch Start Line 9]
Incorrect syntax near 'END'

Upvotes: 1

Views: 2299

Answers (1)

Kyle Martin
Kyle Martin

Reputation: 578

You're missing the final END in your script (before the GO).

Corrected Script:

CREATE PROCEDURE [dbo].[GetUserID]
(   
    -- Add the parameters for the function here
    @UUID varchar(36),
    @SuperID varchar(33)
)
AS
BEGIN
SET NOCOUNT ON
SET ARITHABORT ON

DECLARE @SuperIDExists bit
DECLARE @UserID int

SELECT @SuperIDExists =
    CASE WHEN EXISTS (
    SELECT *
    FROM dbo.[Users] AS u
    WHERE u.SuperID = @SuperID
    )
    THEN CAST(1 as bit)
    ELSE CAST(0 as bit)
END

IF @SuperIDExists = 1
BEGIN
    SELECT @UserID =
    (SELECT u.ID
    FROM dbo.[Users] AS u
    WHERE u.SuperID = @SuperID)
END

ELSE
BEGIN
    SELECT @UserID =
    (SELECT u.ID
    FROM dbo.[Users] as U
    WHERE u.UUID = @UUID)
END
END  --  <-------  ADDED `END`

GO

Upvotes: 4

Related Questions