delete proc not working when vals are NULL

i wrote this delete proc to handle three parms - one required, two are optional.  i have one record in the pricetable where symbol = 11111, and price and lastupdated are NULL
i am firing the proc like this:  exec database.dbo.procname '11111'

but it's not being removed
please let me know the right way to handle the optional parms, yet still get the values from the table when one or the other of the 2nd two parms are NULL

CREATE PROCEDURE dbo.usp_deletePrices
(
       @symbol       varchar(16),
      @price            decimal(18,8)=NULL,
      @lastupdated      datetime=NULL
)
AS
SET NOCOUNT ON
BEGIN
      DELETE dbo.priceTable
      WHERE Symbol = @symbol
      AND Price=ISNULL(@price,price)
      AND LastUpdated=ISNULL(@lastupdated,lastupdated)      
END
IF @@ERROR <> 0
BEGIN
          RAISERROR('Failed to delete from priceTable',16,-1)
          ROLLBACK TRAN
          RETURN
END
SET NOCOUNT OFF


GO
LVL 17
dbaSQLAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
the problem is that when the value (Price) is null, NULL = NULL will NOT return true, but NULL, hence no match!

what about this:

CREATE PROCEDURE dbo.usp_deletePrices
(
       @symbol       varchar(16),
      @price            decimal(18,8)=NULL,
      @lastupdated      datetime=NULL
)
AS
SET NOCOUNT ON
BEGIN
      DELETE dbo.priceTable
      WHERE Symbol = @symbol
      AND (Price= @price OR (Price is null AND @price is null))
      AND (LastUpdated= @lastupdated OR (lastupdated IS NULL and @lastupdated IS NULL))
END
IF @@ERROR <> 0
BEGIN
          RAISERROR('Failed to delete from priceTable',16,-1)
          ROLLBACK TRAN
          RETURN
END
SET NOCOUNT OFF
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
AND (Price = @price OR ISNULL(@Price))
AND (LastUpdated = @LastUpdated OR ISNULL(@LastUpdated))
0
 
dbaSQLAuthor Commented:
jim, error 174: the isnul function requires 2 arguments.
angel, perfect

thank you both very much
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
dbaSQLAuthor Commented:
i'm sorry, angel, may i ask just another thing on this proc?  
if price has a value, but the other is null, it won't work when just passing the symbol

how best do you think i should approach this?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>if price has a value, but the other is null, it won't work when just passing the symbol
how do you call the proc? I guess you are doing "something wrong"...
0
 
dbaSQLAuthor Commented:
exec proc name @symbol = 'xxxx'
(remember, @symbol is required, the other two optional)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
now, does the @price = null mean you want to delete ALL the rows whatever the price value is, or only those with price IS NULL ?

if it means all price values, then the syntax of jimhorn was closer...
anyhow:

CREATE PROCEDURE dbo.usp_deletePrices
(
       @symbol       varchar(16),
      @price            decimal(18,8)=NULL,
      @lastupdated      datetime=NULL
)
AS
SET NOCOUNT ON
BEGIN
      DELETE dbo.priceTable
      WHERE Symbol = @symbol
      AND (Price= @price OR @price is null)
      AND (LastUpdated= @lastupdated @lastupdated IS NULL)
END
IF @@ERROR <> 0
BEGIN
          RAISERROR('Failed to delete from priceTable',16,-1)
          ROLLBACK TRAN
          RETURN
END
SET NOCOUNT OFF
0
 
dbaSQLAuthor Commented:
no, i was just trying to code price and lastupdated optionally
if all three are given, that's it, delete the row where = @symbol,@price,@lastupdated
if only @symbol is given, just get the row(s) for the given @symbol
it could be @symbol
it could be @symbol AND @price
it could be @symbol AND @lastupdate
it could be @symbol AND @price AND @lastupdate

I thought i had coded it that way, but found when price and/or update was NULL, it wasn't removing for the given @symbol.  hence, my initial inquiry
they then told me that for symbol = 1111 where price = 0, but lastupdate is NULL,
exec procname @symbol  
didn't work  (didn't remove the record)

basically, if one of the optional parms has a value, but the other doesn't, it won't delete for the given @symbol

no biggie right now, as they have decided only @symbol is required
but, i would like to know the flaw in my logic, if you see it
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, my last suggestion should solve it...
0
 
dbaSQLAuthor Commented:
yes, i thought the same, angel
i am not sure why they're experiencing failure
i will dig a little further
thank you, though, for getting back to me
0
All Courses

From novice to tech pro — start learning today.