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) A socialist is someone who will give you the shirt off *someone else's* back.
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) A socialist is someone who will give you the shirt off *someone else's* back.
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) A socialist is someone who will give you the shirt off *someone else's* back.
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 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply