MSSQL 添加插入存储过程和其他程序时出错

Question

以下代码出现错误>用户附近的语法不正确

CREATE PROCEDURE [dbo].[Add]
@Name NVARCHAR(50),
@Email NVARCHAR(50),
@Address NVARCHAR (300)
AS
INSERT INTO User([Name],[Email],[Address]) VALUES ("'@Name','@Email','@Address'")
RETURN 0

并在下面的代码下尝试过

CREATE PROCEDURE [dbo].[Add]
@Name NVARCHAR(50),
@Email NVARCHAR(50),
@Address NVARCHAR (300)
AS
INSERT INTO User VALUES ("@Name,@Email,@Address")
RETURN 0

我正在使用下表

CREATE TABLE [dbo].[User] (
[UserId]  INT            NOT NULL,
[Name]    NVARCHAR (50)  NOT NULL,
[Email]   NVARCHAR (50)  NOT NULL,
[Address] NVARCHAR (300) NULL,
PRIMARY KEY CLUSTERED ([UserId] ASC)
);

使用Visual Studio 2019 SQL Server 2016 版本 13.00.4001

我无法找出问题，所以在这里帮助我

Answer 1

User是SQL Server中的保留关键字，使用方括号：

INSERT INTO [User] ([Name],[Email],[Address]) 
VALUES (@Name, @Email, @Address)

此外，还保留存储过程add。最好的选择是重命名table/sp名称。

编辑：

CREATE PROCEDURE [dbo].[Add] (
@Name NVARCHAR(50),
@Email NVARCHAR(50),
@Address NVARCHAR (300) 
)
AS
INSERT INTO [User] ([Name],[Email],[Address]) 
VALUES (@Name, @Email, @Address)

Answer 2

UserId列NOT NULL，这就是您收到错误的原因。

(1(将UserID列设为IDENTITY(自动递增(或可为空

CREATE TABLE [dbo].[User] (
[UserId]  INT            IDENTITY(1,1),
[Name]    NVARCHAR (50)  NOT NULL,
[Email]   NVARCHAR (50)  NOT NULL,
[Address] NVARCHAR (300) NULL,
PRIMARY KEY CLUSTERED ([UserId] ASC)
);

(2( 从 SP 中删除引号，

CREATE PROCEDURE [dbo].[Add]
@Name NVARCHAR(50),
@Email NVARCHAR(50),
@Address NVARCHAR (300)
AS
INSERT INTO [User]([Name],[Email],[Address]) VALUES (@Name,@Email,@Address)
RETURN 0