December 11, 2014 at 4:01 pm
Evil Kraig F (12/11/2014)
@scott: You missed a space in the first condition, near the end. Otherwise, that's a form of how I've done this in the past. However, we used NULL instead of ' '. Was more obvious and less prone to user errors.
"Missed a space"? Not sure what you mean.
I added code to consider NULL, but I commented it out because I wasn't sure if it was relevant to this q, but I didn't ignore it, because it would be a special condition.
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".
December 11, 2014 at 4:31 pm
RETURN (
SELECT CASE
WHEN @optional_letter = ' ' AND EXISTS(SELECT 1 FROM dbo.Test WHERE letter = @letter AND number = @number AND optional_letter <> '')
THEN -1
WHEN @optional_letter > ' ' AND EXISTS(SELECT 1 FROM dbo.Test WHERE letter = @letter AND number = @number AND optional_letter = ' ')
THEN -1
--?WHEN @optional_letter IS NULL AND ...?
ELSE 0
END
)
END
As far as I can tell, that should be <> ' ')
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 12, 2014 at 1:43 am
ScottPletcher (12/11/2014)
SELECT CASE
WHEN @optional_letter = ' ' AND EXISTS(SELECT 1 FROM dbo.Test WHERE letter = @letter AND number = @number AND optional_letter <> '')
THEN -1
WHEN @optional_letter > ' ' AND EXISTS(SELECT 1 FROM dbo.Test WHERE letter = @letter AND number = @number AND optional_letter = ' ')
THEN -1
--?WHEN @optional_letter IS NULL AND ...?
ELSE 0
END
But this will allow duplicates.
December 12, 2014 at 6:59 am
This should do the trick. It validates the Optional letter so it can be either NULL or a letter, checks all the conditions from the first post and makes sure there is no duplicates.
CREATE FUNCTION dbo.CKTest (@letter char(1), @number int, @optionalletter char(1))
RETURNS BIT
AS
BEGIN
RETURN(
SELECT CASE
WHEN @optionalletter NOT LIKE '[a-Z]' OR
(SELECT COUNT(*) FROM dbo.Test WHERE letter = @letter AND number = @number AND (optionalletter IS NULL OR optionalletter = ISNULL(@OptionalLetter,OptionalLetter))) > 1
THEN -1
ELSE 0
END)
END
December 12, 2014 at 8:21 am
Emil Bialobrzeski (12/12/2014)
ScottPletcher (12/11/2014)
SELECT CASE
WHEN @optional_letter = ' ' AND EXISTS(SELECT 1 FROM dbo.Test WHERE letter = @letter AND number = @number AND optional_letter <> '')
THEN -1
WHEN @optional_letter > ' ' AND EXISTS(SELECT 1 FROM dbo.Test WHERE letter = @letter AND number = @number AND optional_letter = ' ')
THEN -1
--?WHEN @optional_letter IS NULL AND ...?
ELSE 0
END
But this will allow duplicates.
There is already a unique index to prevent duplicates, according to the OP's description of the problem. The issue is to avoid allow blank and non-blank, not to deal with duplicates.
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".
December 12, 2014 at 8:21 am
Evil Kraig F (12/11/2014)
RETURN (SELECT CASE
WHEN @optional_letter = ' ' AND EXISTS(SELECT 1 FROM dbo.Test WHERE letter = @letter AND number = @number AND optional_letter <> '')
THEN -1
WHEN @optional_letter > ' ' AND EXISTS(SELECT 1 FROM dbo.Test WHERE letter = @letter AND number = @number AND optional_letter = ' ')
THEN -1
--?WHEN @optional_letter IS NULL AND ...?
ELSE 0
END
)
END
As far as I can tell, that should be <> ' ')
You mean '' vs. ' '? No difference. '' = ' ' = SPACE(2) = SPACE(5).
Edit: '<multiple spaces>' was being auto-adjusted to a single space, so I changed to SPACE(n).
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".
December 12, 2014 at 8:57 am
ScottPletcher (12/12/2014)
Emil Bialobrzeski (12/12/2014)
ScottPletcher (12/11/2014)
SELECT CASE
WHEN @optional_letter = ' ' AND EXISTS(SELECT 1 FROM dbo.Test WHERE letter = @letter AND number = @number AND optional_letter <> '')
THEN -1
WHEN @optional_letter > ' ' AND EXISTS(SELECT 1 FROM dbo.Test WHERE letter = @letter AND number = @number AND optional_letter = ' ')
THEN -1
--?WHEN @optional_letter IS NULL AND ...?
ELSE 0
END
But this will allow duplicates.
There is already a unique index to prevent duplicates, according to the OP's description of the problem. The issue is to avoid allow blank and non-blank, not to deal with duplicates.
I don't see why not have one to deal with all of this? especially if the solution can be even simpler. If we are using already a function in check constraint lets use it fully and wisely.
Emil Bialobrzeski (12/12/2014)
This should do the trick. It validates the Optional letter so it can be either NULL or a letter, checks all the conditions from the first post and makes sure there is no duplicates.
CREATE FUNCTION dbo.CKTest (@letter char(1), @number int, @optionalletter char(1))
RETURNS BIT
AS
BEGIN
RETURN(
SELECT CASE
WHEN @optionalletter NOT LIKE '[a-Z]' OR
(SELECT COUNT(*) FROM dbo.Test WHERE letter = @letter AND number = @number AND (optionalletter IS NULL OR optionalletter = ISNULL(@OptionalLetter,OptionalLetter))) > 1
THEN -1
ELSE 0
END)
END
December 15, 2014 at 6:58 am
Thank you everyone for the input and suggestions. I've decided to go with a trigger, largely because everything is in one place. The check constraint option has the disadvantage of the function being in a separate place from the constraint specification. Also, the triggger just somehow seems more intuitive than calling a UDF.
Viewing 8 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy