SQL Server Performance

Stored Procedure not working

Discussion in 'Getting Started' started by Femi, Feb 23, 2010.

  1. Femi New Member

    I have created a database called Shoppers_Stop. I am trying to create a Stored Procedure for adding a user, however - it is not working at them moment, as it is telling me there is an incorrect syntax - I have tried looking, but cant find it for the life of me - please see belowCREATE
    PROCEDURE usp_adduser/*
    We will put the variables in here, later
    */@EmployeeID
    int,@NationalInsuranceNumber
    nvarchar (15),@LoginID
    nvarchar (256),@ManagerID
    nvarchar (256),@Title
    nvarchar (50),@Birthdate
    smalldatetime,@MaritalStatus
    nvarchar (15),@Gender
    nvarchar (10),@HireDate
    smalldatetime,@VacationHours
    smallint,@SickLeaveHours
    smallint,@FirstName
    nvarchar (MAX),@LastName
    nvarchar (MAX),@OfficePhoneNo
    nvarchar (20),@OfficeEmail
    nvarchar (255),@[Email Address]
    nvarchar (255),@Address1
    nvarchar (MAX),@Address2
    nvarchar (MAX),@City
    nvarchar (MAX),@State
    nvarchar (MAX),@Country
    nvarchar (MAX),@[Post Code]
    nvarchar (MAX),AS
    BEGIN
    INSERT
    INTO EmployeeDetails (
    EmployeeID, NationalInsuranceNumber, LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender, HireDate, VacationHours, SickLeaveHours, FirstName, LastName, OfficePhoneNo, OfficeEmail, [Email Address], Address1, Address2, City, State, Country, [Post Code])VALUES
    (@EmployeeID, @NationalInsuranceNumber, @LoginID, @ManagerID, @Title, @BirthDate, @MaritalStatus, @Gender, @HireDate, @VacationHours, @SickLeaveHours, @FirstName, @LastName, @OfficePhoneNo, @OfficeEmail, @[Email Address], @Address1, @Address2, @City, @State, @Country, @[Post Code])END
    GO
    Error Msg is below
    Msg 102, Level 15, State 1, Procedure usp_adduser, Line 21
    Incorrect syntax near 'nvarchar'.
    Msg 137, Level 15, State 2, Procedure usp_adduser, Line 36
    Must declare the scalar variable "@".
  2. FrankKalis Moderator

    Welcome to the forum!
    SQL Server variable names must comply to the rules for regular identifiers and one of these rules is that embedded spaces and special characters are NOT allowed.
    You can find these rules in the manual when you search for identifiers -> about identifiers.
  3. FrankKalis Moderator

    ...also: You may want to reconsider the excessive use of nvarchar(MAX) all over the place. It's very unlikely that data such as First Name, Last Name, etc... is that long, that they need to be stored in columns that can take up to 2 GB of space.
  4. Subhash_Chandra New Member

    Hello Femi,
    Remove the brackets from @[Email Address] and @[Post Code] and write these variables like @Email_Address, @Post_Code. Remove the comma also from after the last parameter @[Post Code] nvarchar (MAX),
    Regards,
    Subhash Chandrahttp://SQLReality.com/blog/

Share This Page