I have a simple table
EMPLOYEE
ID: int
Name: nchar(20)
Birthday: date
And I create a simple procedure to add new record to EMPLOYEE table
ADD_NEW_EMPLOYEE
CREATE PROCEDURE ADD_NEW_EMPLOYEE
@id INT,
@Name NCHAR(20),
@Birthday DATE
AS
BEGIN
INSERT INTO EMPLOYEE
VALUES(@id, @Name, @Birthday)
END
When I execute the procedure (right-click -> Execute Stored Procedure...), I get an error
Incorrect Syntax near '-'
on the value of date.
The code execution panel:
EXEC @return_value = [dbo].[ADD_NEW_EMPLOYEE]
@id = 3,
@Name = N'James Bond',
@Birthday = 1995-06-06
However, if I change the @Birthday type in ADD_NEW_EMPLOYEE to nchar type.
The value is encoded as @Birthday = N'1995-06-06' and the procedure executes normally. But doing so make the declaration in the procedure not corresponding with the value type in the table.
Can you show me the way to fix this ?
The version I use is Microsoft SQL Server 2014
Please note that I execute the procedure with SQL Server (right-click on the procedure and choose "Execute Stored Procedure"), a table to fill value show up and i just have to fill in the data for variables.
#UPDATE:
So in the table to input value, I have to input '1995-06-06' with single quotes instead of 1995-06-06 without the quotes.
What if another program like C# execute this procedure and the caller function forget to enclose the date in quotes? Is this a bug in SQL Server 2014? I feel like a joke.
Syntax error on execute:
This will work, but too clumsy... (notice the quotes ' ' surround the date value)
If you put a value to nchar variable, on execution, the value will automatically enclosed in the quotes ' ' . For the date type, the value goes straight to execute without proper quotes enclosing. And that causes the problem.
