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 "@".
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.
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/
