SQL 操作数类型冲突：uniqueidentifier 与 int 不兼容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7389687/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Operand type clash: uniqueidentifier is incompatible with int
提问by hughesdan
When I attempt to create the stored procedure below I get the following error:
当我尝试创建下面的存储过程时，出现以下错误：
Operand type clash: uniqueidentifier is incompatible with int
操作数类型冲突：uniqueidentifier 与 int 不兼容
It's not clear to me what is causing this error. UserID is in fact an int in all of my tables. Can someone tell me what I've done wrong?
我不清楚是什么导致了这个错误。UserID 实际上在我的所有表中都是一个 int。有人能告诉我我做错了什么吗？
create procedure dbo.DeleteUser(@UserID int)
as
delete from [aspnet_Membership] where UserId = @UserID
delete from [Subscription] where UserID = @UserID
delete from [Address] where UserID = @UserID
delete from [User] where UserID = @UserID
go
采纳答案by Aaron Bertrand
Sounds to me like at least one of those tables has defined UserIDas a uniqueidentifier, not an int. Did you check the data in each table? What does SELECT TOP 1 UserID FROMeach table yield? An intor a GUID?
在我看来，这些表中至少有一个已定义UserID为 a uniqueidentifier，而不是int. 你检查了每个表中的数据吗？这是什么SELECT TOP 1 UserID FROM每个表的收益呢？一个int或一个GUID？
EDIT
编辑
I think you have built a procedure based on all tables that contain a column named UserID. I think you should not have included the aspnet_Membershiptable in your script, since it's not really one of "your" tables.
我认为您已经基于包含名为 UserID 的列的所有表构建了一个过程。我认为您不应该aspnet_Membership在脚本中包含该表，因为它实际上并不是“您的”表之一。
If you meant to design your tables around the aspnet_Membershipdatabase, then why are the rest of the columns intwhen that table clearly uses a uniqueidentifierfor the UserIDcolumn?
如果您打算围绕aspnet_Membership数据库设计表，那么int当该表清楚地使用 auniqueidentifier作为列时，为什么其余的UserID列呢？
回答by user280868
If you're accessing this via a View then try sp_recompileor refreshing views.
如果您通过视图访问它，请尝试sp_recompile或刷新视图。
sp_recompile:
sp_recompile：
Causes stored procedures, triggers, and user-defined functions to be recompiled the next time that they are run. It does this by dropping the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure or trigger is run. In a SQL Server Profiler collection, the event SP:CacheInsert is logged instead of the event SP:Recompile.
使存储过程、触发器和用户定义的函数在下次运行时重新编译。它通过从过程缓存中删除现有计划来强制在下次运行过程或触发器时创建新计划来实现这一点。在 SQL Server Profiler 集合中，记录事件 SP:CacheInsert 而不是事件 SP:Recompile。
Arguments
参数
[ @objname= ] 'object'
The qualified or unqualified name of a stored procedure, trigger, table, view, or user-defined function in the current database. object is nvarchar(776), with no default. If object is the name of a stored procedure, trigger, or user-defined function, the stored procedure, trigger, or function will be recompiled the next time that it is run. If object is the name of a table or view, all the stored procedures, triggers, or user-defined functions that reference the table or view will be recompiled the next time that they are run.
当前数据库中存储过程、触发器、表、视图或用户定义函数的限定或非限定名称。对象是 nvarchar(776)，没有默认值。如果 object 是存储过程、触发器或用户定义函数的名称，则该存储过程、触发器或函数将在下次运行时重新编译。如果 object 是表或视图的名称，则所有引用该表或视图的存储过程、触发器或用户定义的函数将在下次运行时重新编译。
Return Code Values
返回代码值
0 (success) or a nonzero number (failure)
0（成功）或非零数（失败）
Remarks
评论
sp_recompilelooks for an object in the current database only.
sp_recompile仅在当前数据库中查找对象。
The queries used by stored procedures, or triggers, and user-defined functions are optimized only when they are compiled. As indexes or other changes that affect statistics are made to the database, compiled stored procedures, triggers, and user-defined functions may lose efficiency. By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries.
存储过程、触发器和用户定义函数使用的查询仅在编译时进行优化。由于对数据库进行了索引或其他影响统计的更改，编译的存储过程、触发器和用户定义的函数可能会失去效率。通过重新编译作用于表的存储过程和触发器，您可以重新优化查询。
回答by Bingoabs
The reason is that the data doesn't match the datatype. I have come across the same issues that I forgot to make the fields match. Though my case is not same as yours, but it shows the similar error message.
原因是数据与数据类型不匹配。我遇到了忘记使字段匹配的相同问题。虽然我的情况和你的不一样，但它显示了类似的错误信息。
The situation is that I copy a table, but accidently I misspell one field, so I change it using the ALTERafter creating the database. And the order of fields in both table is not identical. so when I use the INSERT INTO TableName SELECT * FROM TableName, the result showed the similar errors: Operand type clash: datetime is incompatible with uniqueidentifier
情况是我复制了一个表，但不小心我拼错了一个字段，所以我ALTER在创建数据库后使用它进行了更改。并且两个表中的字段顺序并不相同。所以当我使用 时INSERT INTO TableName SELECT * FROM TableName，结果显示了类似的错误：Operand type clash: datetime is incompatible with uniqueidentifier
This is a simiple example:
这是一个简单的例子：
use example
go
create table Test1 (
id int primary key,
item uniqueidentifier,
inserted_at datetime
)
go
create table Test2 (
id int primary key,
inserted_at datetime
)
go
alter table Test2 add item uniqueidentifier;
go
--insert into Test1 (id, item, inserted_at) values (1, newid(), getdate()), (2, newid(), getdate());
insert into Test2 select * from Test1;
select * from Test1;
select * from Test2;
The error message is:
错误信息是：
Msg 206, Level 16, State 2, Line 24
Operand type clash: uniqueidentifier is incompatible with datetime
