May 1, 2018 at 6:00 am
I am trying to write a UDF that is used by a check constraint but seem to keep running into an issue.
When having a complete empty table the following results in 1declare @a float = (SELECT max(amount) FROM Bid WHERE auctionId = 1)
if(@a is null) select 1
else select 0
But when I try to implement similar logic into an UDF it seems to return 0 every time running it against an empty table.CREATE FUNCTION dbo.fn_ck_newBidIsHigher(@auction int, @bid numeric(8,2))
RETURNS bit
BEGIN
DECLARE @currentHighestBid float = (SELECT max(amount) FROM Bid WHERE auctionId = @auction)
IF((@bid > @currentHighestBid) OR (@currentHighestBid is null))
BEGIN
RETURN 1
END
RETURN 0
END
GO
I've been looking at it for over an hour now (maybe that's the problem), but I can't figure out where it's going wrong.
I am calling the function in the check constraint as follows:ALTER TABLE Bid ADD CONSTRAINT ck_New_Bid_Must_Be_Higher_Than_Previous_Bid CHECK(dbo.fn_ck_newBidIsHigher(auctionId, amount) = 1)
May 1, 2018 at 6:12 am
Try
If (condition)
Return 1
else
Return 0;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 1, 2018 at 6:16 am
Phil Parkin - Tuesday, May 1, 2018 6:12 AMTry
If (condition)
Return 1
else
Return 0;
That result in the same issue next to having two return statements that return 0 at the end of the code (last statement must be a return).CREATE FUNCTION dbo.fn_ck_newBidIsHigher(@auction int, @bid numeric(8,2))
RETURNS bit
BEGIN
DECLARE @currentHighestBid float = (SELECT max(amount) FROM Bid WHERE auctionId = @auction)
IF((@bid > @currentHighestBid) OR (@currentHighestBid is null))
BEGIN
RETURN 1
END
ELSE
BEGIN
RETURN 0
END
RETURN 0
END
GO
May 1, 2018 at 6:24 am
Shows how often I create UDFs (they are notoriously bad performers, in case you did not know).
CREATE FUNCTION dbo.fn_ck_newBidIsHigher
(
@auction INT
, @bid NUMERIC(8, 2)
)
RETURNS BIT
BEGIN
DECLARE @Ret BIT = 0;
DECLARE @currentHighestBid FLOAT = (
SELECT MAX(amount)
FROM Bid
WHERE auctionId = @auction
);
IF (
(@bid > @currentHighestBid)
OR (@currentHighestBid IS NULL)
)
SET @Ret = 1;
RETURN @Ret;
END;
GO
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 1, 2018 at 6:58 am
Does not work either.
Maybe my thinking process is completely wrong so let me describe the problem.
When a new bid is inserted into the table I need to check if the bid follows the company's rules.
The rule is that a new bid must be at least 5 dollars higher than the previous highest bid if the previous highest bid was below 100 dollar in all other cases the bid must be at least 10 dollars higher.
May 1, 2018 at 7:16 am
Can you please post the DDL (create table) script, sample data as an insert statement and the expected results?
😎
My advice is to not use a scalar function as the execution overhead is far greater than the one of the inline table valued function, often the difference is in the order of the second or third magnitude.
May 1, 2018 at 7:20 am
As your code contains nothing which deals with the 5/10 dollar increments, I'd say that you were right in your assessment 🙂
Try this CREATE FUNCTION dbo.fn_ck_newBidIsHigher
(
@auction INT
, @bid NUMERIC(8, 2)
)
RETURNS BIT
BEGIN
DECLARE @Ret BIT = 0;
DECLARE @currentHighestBid FLOAT = (
SELECT MAX(amount)
FROM Bid
WHERE auctionId = @auction
);
DECLARE @MinIncrement INT = 10;
IF @currentHighestBid < 100
SET @MinIncrement = 5;
IF (
(@bid > @currentHighestBid + @MinIncrement)
OR (@currentHighestBid IS NULL)
)
SET @Ret = 1;
RETURN @Ret;
END;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 1, 2018 at 7:36 am
Student031 - Tuesday, May 1, 2018 6:58 AMDoes not work either.
Maybe my thinking process is completely wrong so let me describe the problem.
When a new bid is inserted into the table I need to check if the bid follows the company's rules.
The rule is that a new bid must be at least 5 dollars higher than the previous highest bid if the previous highest bid was below 100 dollar in all other cases the bid must be at least 10 dollars higher.
Are you doing that validation before or after inserting it?
May 1, 2018 at 8:00 am
ZZartin - Tuesday, May 1, 2018 7:36 AMStudent031 - Tuesday, May 1, 2018 6:58 AMDoes not work either.
Maybe my thinking process is completely wrong so let me describe the problem.
When a new bid is inserted into the table I need to check if the bid follows the company's rules.
The rule is that a new bid must be at least 5 dollars higher than the previous highest bid if the previous highest bid was below 100 dollar in all other cases the bid must be at least 10 dollars higher.Are you doing that validation before or after inserting it?
It's performed in a check so I presume those are fired before insertion.
May 1, 2018 at 8:03 am
Eirikur Eiriksson - Tuesday, May 1, 2018 7:16 AMCan you please post the DDL (create table) script, sample data as an insert statement and the expected results?
😎
My advice is to not use a scalar function as the execution overhead is far greater than the one of the inline table valued function, often the difference is in the order of the second or third magnitude.
Given that they are using this for a check constraint I don't think you can use an inline table valued function. At least I don't know how to do that.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 1, 2018 at 8:06 am
Phil Parkin - Tuesday, May 1, 2018 7:20 AMAs your code contains nothing which deals with the 5/10 dollar increments, I'd say that you were right in your assessment 🙂
Try thisCREATE FUNCTION dbo.fn_ck_newBidIsHigher
(
@auction INT
, @bid NUMERIC(8, 2)
)
RETURNS BIT
BEGIN
DECLARE @Ret BIT = 0;
DECLARE @currentHighestBid FLOAT = (
SELECT MAX(amount)
FROM Bid
WHERE auctionId = @auction
);
DECLARE @MinIncrement INT = 10;IF @currentHighestBid < 100
SET @MinIncrement = 5;IF (
(@bid > @currentHighestBid + @MinIncrement)
OR (@currentHighestBid IS NULL)
)
SET @Ret = 1;RETURN @Ret;
END;
I am aware of that trying to build it incrementally.
It seems to go wrong here:
DECLARE @currentHighestBid FLOAT = (
SELECT MAX(amount)
FROM Bid
WHERE auctionId = @auction
);
For some reason it does not store NULL into the variable even know running the query outside of the UDF does return NULL.
Anyway, when looking at the variable inside the IF it goes wrong, it skippes the if and retruns 0.
Following code works perfectly as well untill I place it in an UDF.DECLARE @a numeric(8,2) = ISNULL((SELECT max(amount) FROM Bid WHERE auctionId = 1), 0.00)
select @a
Is there a way to print the content of a variable inside a UDF while it is executing in SSMS, might clear things up to see what is happening step by step?
May 1, 2018 at 8:10 am
Here is another way to handle this. I think this is a lot simpler than creating so many variables to check. The way the sql server engine works is that if any number other than 0 is converted to a bit it will be 1. Also, I would be careful choosing the correct datatype. Float is not a good choice for storing numbers that have to deal with money because it is an approximate datatype. Instead something numeric(9,2) is a better choice.
create function dbo.fn_ck_newBidIsHigher(@auction int, @bid numeric(8,2))
returns bit as
begin
declare @HighestBid bit
select @HighestBid = count(*)
from Bid
where AuctionID = @auction
and @bid >= (select max(amount) + case when max(amount) <= 100 then 5 else 10 end from Bid where AuctionID = @auction)
return @HighestBid
end
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 1, 2018 at 9:51 am
Local variables tend to slow down scalar functions, so avoid them if possible. In this case, they also make the logic much more complicated than it needs to be. Limit the comparisons to those you actually need to make to do the task in mind.
CREATE FUNCTION dbo.fn_ck_newBidIsHigher
(
@auction int,
@bid numeric(8,2)
)
RETURNS bit
AS
BEGIN
RETURN (
SELECT CASE WHEN NOT EXISTS(SELECT 1 FROM Bid WHERE auctionId = @auction) OR
@bid > (SELECT MAX(amount) FROM Bid WHERE auctionId = @auction)
THEN 1 ELSE 0 END
)
END
GO
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 1, 2018 at 10:30 am
Sean Lange - Tuesday, May 1, 2018 8:10 AMHere is another way to handle this. I think this is a lot simpler than creating so many variables to check. The way the sql server engine works is that if any number other than 0 is converted to a bit it will be 1. Also, I would be careful choosing the correct datatype. Float is not a good choice for storing numbers that have to deal with money because it is an approximate datatype. Instead something numeric(9,2) is a better choice.
create function dbo.fn_ck_newBidIsHigher(@auction int, @bid numeric(8,2))
returns bit as
begin
declare @HighestBid bitselect @HighestBid = count(*)
from Bid
where AuctionID = @auction
and @bid >= (select max(amount) + case when max(amount) <= 100 then 5 else 10 end from Bid where AuctionID = @auction)return @HighestBid
end
Let's go the rest of the way on functionality, too:CREATE FUNCTION dbo.fn_ck_newBidIsHigher (
@auction int,
@bid numeric(8,2)
)
RETURNS bit
AS
BEGIN
RETURN (
SELECT
CASE
WHEN NOT EXISTS (
SELECT 1
FROM Bid
WHERE auctionId = @auction
)
OR @bid > (
SELECT MAX(amount) +
CASE
WHEN MAX(amount) <= 100 THEN 5.
ELSE 10.
END
FROM Bid
WHERE auctionId = @auction
)
THEN 1
ELSE 0
END
);
END
GO
I happen to prefer some expansion of code that is often buried deep in a CASE expression, so I always indent my CASE expression elements and put things on separate lines. It almost always makes it easier to read and maintain.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 1, 2018 at 10:39 am
I haven't tested, but this should work.
CREATE FUNCTION dbo.fn_ck_newBidIsHigher(@auction int, @bid numeric(8,2))
RETURNS bit
BEGIN
RETURN (SELECT CAST(CASE WHEN EXISTS( SELECT 1 FROM Bid WHERE auctionId = @auction AND amount >= @bid)
THEN 0 ELSE 1 END AS BIT))
END
GO
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply