存储过程错误：“将数据类型nvarchar转换为uniqueidentifier时出错”

So I'm new to creating SPs and right now I am trying to create an SP to insert values into my table Report below.

所以我是创建SP的新手，现在我正在尝试创建一个SP，将值插入到我的表格报告中。

CREATE TABLE Report (
    ID UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,
    STAFF VARCHAR(1000)NOT NULL,
    EMAIL VARCHAR(1000)NOT NULL,
    LASTCHANGE DATE NOT NULL
)

CREATE PROCEDURE spInsertOrUpdate(
    @ID UNIQUEIDENTIFIER,
    @STAFF VARCHAR(1000),
    @EMAIL VARCHAR(1000),
    @LASTCHANGE DATETIME
) AS
BEGIN
    INSERT INTO Report(ID, STAFF, EMAIL, LASTCHANGE)
        VALUES(@ID, @STAFF, @EMAIL, @LASTCHANGE)
END

EXEC spInsertOrUpdate NEWID, 'Evlyn Dawson', 'evdawson@gmail.com', GETDATE

Right After executing the SP I following error:

正确执行SP后跟随错误：

Msg 8114, Level 16, State 5, Procedure spInsertOrUpdate, Line 0 Error converting data type nvarchar to uniqueidentifier

消息8114，级别16，状态5，过程spInsertOrUpdate，行0错误将数据类型nvarchar转换为uniqueidentifier

Can someone please help me out with this issue?

有人可以帮我解决这个问题吗？

4 个解决方案

#1

So if your just calling your stored procedure with getdate and newid why dont you just add them as default on your table?

因此，如果您只是使用getdate和newid调用存储过程，为什么不将它们作为默认值添加到您的表中？

Table

表

  CREATE TABLE [dbo].[Report](
    [ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Report_ID]  DEFAULT (newid()),
    [STAFF] [varchar](1000) NOT NULL,
    [EMAIL] [varchar](1000) NOT NULL,
    [LASTCHANGE] [datetime] NOT NULL CONSTRAINT [DF_Report_LASTCHANGE]  DEFAULT (getdate()),
 CONSTRAINT [PK__Report__3214EC27D2D8BF72] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Procedure

程序

create PROCEDURE spInsertOrUpdate(

@STAFF VARCHAR(1000),
@EMAIL VARCHAR(1000)

) AS
BEGIN
INSERT INTO Report(STAFF, EMAIL)
    VALUES(@STAFF, @EMAIL)
END

Execute statement

执行语句

EXEC spInsertOrUpdate  'Evlyn Dawson', 'evdawson@gmail.com'

Edit Please also note that your lastchanged column is of type DATE, however if you want date with timestamp you should use datetime

编辑请注意，您的lastchanged列的类型为DATE，但是如果您想要带有时间戳的日期，则应使用datetime

#2

This error message is a bit of a wild goose chase, the problem is that both NEWID() and GETDATE() are functions, so require parentheses. Unforunately, you cannot pass a function as a parameter to a stored procedure, so you would first need to assign the values to a variable:

这个错误消息是一个疯狂的追逐，问题是NEWID（）和GETDATE（）都是函数，所以需要括号。不幸的是，您无法将函数作为参数传递给存储过程，因此您首先需要将值分配给变量：

DECLARE @ID UNIQUEIDENTIFIER = NEWID(),
        @Date DATE = GETDATE();

EXEC #spInsertOrUpdate @ID, 'Evlyn Dawson', 'evdawson@gmail.com', @Date;

As an aside, a UNIQUEIDENTIFIER column is a very poor choice for a clustering key

另外，UNIQUEIDENTIFIER列对于群集密钥来说是一个非常糟糕的选择

#3

I would start by calling the functions correctly:

我首先要正确调用函数：

EXEC spInsertOrUpdate NEWID(), 'Evlyn Dawson', 'evdawson@gmail.com', GETDATE();

NEWID() and GETDATE() are functions, so you need parentheses after them.

NEWID（）和GETDATE（）是函数，因此您需要括号。

However, I don't think the lack of parentheses would cause that particular error. You would need to set variables first, and then use them for the exec.

但是，我认为缺少括号会导致该特定错误。您需要先设置变量，然后将它们用于exec。

EDIT:

编辑：

A better approach is to set the ids and dates automatically:

更好的方法是自动设置ID和日期：

CREATE TABLE Report (
    ID UNIQUEIDENTIFIER PRIMARY KEY NOT NULL DEFAULT NEWID(),
    STAFF VARCHAR(1000) NOT NULL,
    EMAIL VARCHAR(1000) NOT NULL,
    LASTCHANGE DATE NOT NULL DEFAULT GETDATE()
);

CREATE PROCEDURE spInsertOrUpdate (
    @STAFF VARCHAR(1000),
    @EMAIL VARCHAR(1000)
) AS
BEGIN
    INSERT INTO Report(STAFF, EMAIL)
        VALUES (@STAFF, @EMAIL)
END;

EXEC spInsertOrUpdate 'Evlyn Dawson', 'evdawson@gmail.com';

I would also discourage you from using unique identifiers as primary keys in the table. They are rather inefficient, because they can lead to page fragmentation. Use an identity column instead.

我也不鼓励你使用唯一标识符作为表中的主键。它们效率很低，因为它们可能导致页面碎片化。请改用标识列。

#4

Thanks For all your help.I finally found a proper way of doing this within the SP and I got a proper understanding of SPs now.This is how I resolved the issue

谢谢你的帮助。我终于在SP中找到了正确的方法，我现在对SP有了正确的理解。这就是我解决问题的方法

CREATE PROCEDURE spInsertOrUpdate(@STAFF VARCHAR(1000),@EMAIL VARCHAR(1000),@CARS VARCHAR(1000))

AS
BEGIN
INSERT INTO Report(ID,STAFF,EMAIL,CARS,LASTCHANGE)
VALUES(NEWID(),@STAFF,@EMAIL,@CARS,GETDATE())
END

EXEC spInsertOrUpdate 'Evlyn Dawson','evdawson@gmail.com','Ferrari'

Note that I have also a CARS column

请注意，我还有一个CARS专栏

#1