Menu
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 
572
To fix this issue, either change the date value to a datetime value or use the DATEADD() function.
The globally unique identifier (GUID) data type in SQL Server is represented by the uniqueidentifier data type, which stores a 16-byte binary value. A GUID is a binary number, and its main use is as an identifier that must be unique in a network that has many computers at many sites.
It happens when you're trying to insert data into a column that is incompatible with the data type you're trying to insert. This could happen if you accidentally try to insert data into the wrong column (or even the wrong table).
Just between the Insert section and Values keyword. There is a query statement to output the inserted Guid into the variable table. Then to get the value itself you can see the last query where we select the ColGuid from the variable table.
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?
128
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.
39
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
