Stored procedure delete query

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,

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

Result: 0 row (s) affected.

But, 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?

Answer 1

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 2

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.