CODEWITHSUNDEEP
databasestored-proceduressql-delete
Mehmet
Mehmet

Reputation: 2278

Stored procedure delete query

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?

Upvotes: 5

Views: 11314

Answers (7)

Michael Haren
Michael Haren

Reputation: 108236

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.

Upvotes: 4

Mehmet
Mehmet

Reputation: 2278

I solved it.

Solution: ALTER PROCEDURE [dbo].[k_ShoppingCart_DELETE]

@cartGUID nvarchar (50)

Upvotes: 0

Thorsten
Thorsten

Reputation: 13181

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.

Upvotes: 0

beach
beach

Reputation: 8630

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.

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1062560

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.

Upvotes: 3

Dave Markle
Dave Markle

Reputation: 97671

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.

Upvotes: 0

Noel Kennedy
Noel Kennedy

Reputation: 12258

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

Upvotes: 0

Related Questions