Stored Procedure not working | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
Stored Procedure not workingI 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
We will put the variables in here, later
nvarchar (255),@[Email Address]
nvarchar (MAX),@[Post Code]
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
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 "@".
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.
…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.
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),