Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Location
    Los Angeles, CA
    Posts
    6

    Unhappy Unanswered: If Exists Statement In Stored Procedure

    Hello all!

    Newbie question:

    There appears to be something wrong with this syntax in SQL Server 2000:

    CREATE PROCEDURE spAddNewUser

    @UserName varchar (50),
    @Password varchar (10),
    @NewUserID int = null OUTPUT

    AS


    IF EXISTS (SELECT * FROM Security WHERE UserName = @UserName)

    I can't get past the last "if exists" statement, without getting a syntax error when checking syntax. I get "ERROR: Incorrect Syntax near ')'.

    I'm sure it's a very simple mistake...

    Thanks in advance for any help :-)

  2. #2
    Join Date
    Aug 2002
    Location
    UK
    Posts
    87
    I think the IF statement is expecting some more code.

    if i add the code

    begin
    print 'yes'
    end

    after your code then i don't get any parse errors.

    What extra code do you want to put in, as your stored procedure does not do anything at the mo

  3. #3
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I don't know if the code you have shown is what you have, but if it is you are missing the brackets around the paramter list.

    CREATE PROCEDURE spAddNewUser
    (
    @UserName varchar (50),
    @Password varchar (10),
    @NewUserID int = null OUTPUT
    )
    AS

    IF EXISTS (SELECT * FROM Security WHERE UserName = @UserName)


    Also don't forget to use BEGIN and END if you need to run a block of code when the IF statment is true.
    MCDBA

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Try this way:

    CREATE PROCEDURE spAddNewUser
    @UserName varchar (50),
    @Password varchar (10),
    @NewUserID int = null OUTPUT
    AS
    IF EXISTS (SELECT * FROM Security WHERE UserName = @UserName)
    select 'exists!'
    ELSE
    select 'not exists!'

  5. #5
    Join Date
    Aug 2002
    Location
    Los Angeles, CA
    Posts
    6

    Thanks! oops...

    all of you are correct....I guess it was just waiting for more info (what happens AFTER the IF EXISTS statement).

    I just went ahead and completed the code and it was fine.

    <blush>

    :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •