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(@lastup
END
IF @@ERROR <> 0
BEGIN
RAISERROR('Failed to delete from priceTable',16,-1)
ROLLBACK TRAN
RETURN
END
SET NOCOUNT OFF
GO
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(@lastup
END
IF @@ERROR <> 0
BEGIN
RAISERROR('Failed to delete from priceTable',16,-1)
ROLLBACK TRAN
RETURN
END
SET NOCOUNT OFF
GO
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
jim, error 174: the isnul function requires 2 arguments.
angel, perfect
thank you both very much
angel, perfect
thank you both very much
ASKER
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?
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?
>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"...
how do you call the proc? I guess you are doing "something wrong"...
ASKER
exec proc name @symbol = 'xxxx'
(remember, @symbol is required, the other two optional)
(remember, @symbol is required, the other two optional)
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
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
ASKER
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,@lastupdate
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
if all three are given, that's it, delete the row where = @symbol,@price,@lastupdate
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
well, my last suggestion should solve it...
ASKER
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
i am not sure why they're experiencing failure
i will dig a little further
thank you, though, for getting back to me
AND (LastUpdated = @LastUpdated OR ISNULL(@LastUpdated))