如何在存储过程中添加userID作为uniqueidentifier数据类型

Question

我创建了一个存储过程，但当我尝试使用它时，我遇到了错误。

"Msg 8114，Level 16，State 5，Procedure SP_Customer_Add，Line 0[批次起始行36]将数据类型varchar转换为时出错uniqueidentifier。">

alter proc SP_Customer_Add (
@UserID uniqueidentifier,
@Firstname nvarchar(30),
@Surname nvarchar(30),
@CardNumber nvarchar(16),
@Password nvarchar(4),
@City nvarchar(13), 
@Birthdate smalldatetime,
@Email nvarchar(30), 
@PhoneNumber nvarchar (10),
@Balance money 
)
as 
begin 
set nocount on;
insert into Customers (
[UserID],
[Firstname], 
[Surname], 
[CardNumber], 
[Password], 
[City], 
[Birthdate], 
[Email], 
[PhoneNumber], 
[Balance] 
) values (
@UserID,
@Firstname,
@Surname,
@CardNumber,
@Password,
@City, 
@Birthdate,
@Email , 
@PhoneNumber ,
@Balance
)
select @UserID  = newID();
END

我把这个过程称为：

exec SP_Customer_Add 
'Cem',
'Yücel',
'1234567891234567',
'1111','Malatya',
'1999/12/5',
'[e-mail]',
'5421234312',
10000

Answer 1

如果插入带有GUID的行，那么让RDBMS处理生成。我假设ID也是CLUSTERED INDEX，所以更改它，使其具有默认值：

ALTER TABLE dbo.Customers ADD CONSTRAINT DF_UserID DEFAULT NEWSEQUENTIALID() FOR UserID;

然后DROP您的SP，并用名称Customer_Add重新创建它(正如我的评论所说，因为有前缀是个问题(，然后在不引用UserID作为变量或INSERT的情况下重新创建SP。

Answer 2

调用过程SP_Customer_Add 时，您没有传递参数(@UserID(的值

exec SP_Customer_Add 
'Cem', 
'Yücel', 
'1234567891234567', 
'1111','Malatya',
'1999/12/5',
'[e-mail]',
'5421234312',
10000

以下是更改后的程序：

alter proc SP_Customer_Add
(
@Firstname nvarchar(30),
@Surname nvarchar(30),
@CardNumber nvarchar(16),
@Password nvarchar(4),
@City nvarchar(13), 
@Birthdate smalldatetime,
@Email nvarchar(30), 
@PhoneNumber nvarchar (10),
@Balance money 
)
as 
begin 
set nocount on;
DECLARE @UserID uniqueidentifier
SET @UserID = NEWID()
insert into Customers ([UserID],[Firstname], [Surname], [CardNumber], [Password], [City], [Birthdate], [Email], [PhoneNumber], [Balance] )
values
(
@UserID,
@Firstname,
@Surname,
@CardNumber,
@Password,
@City, 
@Birthdate,
@Email , 
@PhoneNumber ,
@Balance
)
END