Translate of the Code statement

Question

Hi, what is the exact logic of the following SP code. I want to translate this logic in Select statement to know the following logic and know the reason of the error which is
'Code field is not unique'. Thanks

/* cannot insert code in "CodeSection" if "code" is not unique */

DECLARE @Codenum VARCHAR(256)
declare @acount int

SELECT @Codenum=',111222222,111222233,'

select @acount=count(*) from CodeSection t2
where [Codenum] is not NULL
AND ISNULL(@Codenum, '') NOT LIKE '%,' + ISNULL([code], '') + ',%'
if @acount > 1
raiserror('Code field is not unique', 16, 10)
return

Answer 1

I think that the approximate translation is: “if the CodeSection table contains two or more rows where Codenum column is not null and code column is not 111222222 and not 111222233, then ‘Code field is not unique’”.

If you need a different functionality, then the query can be adjusted.

Answer 2

The code does not really make sense to me. First, if this is from a stored procedure, why is @Codenum set to a constant value before checking?

Even if we overlook that: The operator is NOT LIKE, so this means that as we find two rows which do not match @Codenum, we start yelling about the code not being unique.

LIKE would make a little more sense. Then you have a comma-separated string, which also has commas in the start and the end, and this compared to a pattern with a leading and closing wildcard and with commas on both sides. So if a row has code 123, you would be comparing @Codenum to %,123,%, and if @Codenum goes ,123,456,789,, there is a match.

But even then it's kind of funny. Since the condition is @account > 1, this means that one duplicate is permitted - but not two.

Also, the code does not consider that there may be duplicates in @Codenum itself.