Operand type clash uniqueidentifier is incompatible with int

Question

When I attempt to create the stored procedure below I get the following error      Operand type clash  uniqueidentifier is incompatible with 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   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

User · Accepted Answer

Sounds to me like at least one of those tables has defined UserID as a uniqueidentifier, not an int. Did you check the data in each table? What does SELECT TOP 1 UserID FROM each table yield? An int or a 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_Membership table in your script, since it's not really one of "your" tables.

If you meant to design your tables around the aspnet_Membership database, then why are the rest of the columns int when that table clearly uses a uniqueidentifier for the UserID column?

User · Answer

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 ALTER after 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  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

User · Answer

If you re accessing this via a View then try sp recompile or refreshing views   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   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   Return Code Values  0  success  or a nonzero number  failure   Remarks  sp recompile looks for an object in the current database only   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

[sql] Operand type clash: uniqueidentifier is incompatible with int

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to asp.net-membership