Stored procedure Delete query problem

Question

I have stored procedure:

ALTER PROCEDURE [dbo].[k_ShoppingCart_DELETE]

@cartGUID nvarchar

AS

DELETE FROM
  [dbo].[k_ShoppingCart]
WHERE
  CartGUID = @cartGUID

When I execute this sp;

exec dbo.k_ShoppingCart_DELETE '32390b5b-a35a-4e32-8393-67d5629192f0'

result: 0 row (s) affected

Actually when I try this query:

Delete FROM k_ShoppingCart Where CartGUID = '32390b5b-a35a-4e32-8393-67d5629192f0'

result: 2 rows affected

What is wrong with this ?

Thanks.

Answer 1

Your stored proc SQL seems a bit strange, did you copy it properly?

This doesn't look like it will compile to be honest:

WHERE
  CartGUID =@cartGUID

in (Select

Answer 2

What's with that IN statement at the end of your stored proc? It doesn't make any sense. Just get rid of it. Also, prefix all of your tables with "dbo" in the query and in the sproc (who knows, you might have another version of the same table in your default schema). Doing those two things should do the trick.

Answer 3

Should @cartGUID be a uniqueidentifier rather than an nvarchar? It is more efficient to cast a single varchar to a guid and compare the guids than it is to cast all the guids to varchar, and hope that it uses the same format (else equality will fail anyway).

As others have pointed out, the WHERE clause looks funky, but my money is on the varchar conversion being the culprit.

Answer 4

I choose cartGUID nvarchar type. But this is not the problem. I try to Delete a City with City.Name in City table, it doesnt work too.

Just working with CityID. Just working CartID.

Answer 5

I couldnt solve it ?

Answer 6

I don't suppose you have the same table under multiple schemas...

dbo.k_ShoppingCart

vs.

user1.k_ShoppingCart

The stored procedure is using "dbo" while the ad-hoc query is using the current user reference.

Answer 7

One more thing that looks strange to me: If cartID is a GUID (and a unique identifier) why does your delete statement affect two rows?

Also, try adding a length to the definition of cartID, maybe some funky default length definition leads to the input parameter being shortened or filled with blanks or something.

Answer 8

If you insist on using NVARCHAR instead of UNIQUEIDENTIFIER, you need to specify the size:

@cartGUID nvarchar(36)

Without it, your guids are being truncated (to 30 characters).

You can confirm this behavior by running this modified version of your working query:

DECLARE @cart nvarchar, @sizedcart nvarchar(36)
SET @cart      = '32390b5b-a35a-4e32-8393-67d5629192f0'
SET @sizedcart = '32390b5b-a35a-4e32-8393-67d5629192f0'

-- works
Delete FROM k_ShoppingCart Where CartGUID = '32390b5b-a35a-4e32-8393-67d5629192f0'

-- will not work
Delete FROM k_ShoppingCart Where CartGUID = @cart

-- should work
Delete FROM k_ShoppingCart Where CartGUID = @sizedcart

I agree with @Marc Gravell, though, uniqueidentifier is the way to go here.

Answer 9

I solved it.

Solution: ALTER PROCEDURE [dbo].[k_ShoppingCart_DELETE]

@cartGUID nvarchar (50)