T-SQL get count of differences between two tables

Question

I have a function that will get two lists of IDs and I want to get back the count of differences. If both lists are equal, then 0, otherwise the count.

This I will call:

SELECT [dbo].[Redline_compareBaseProjSuccessors]
( '2498,2502,2510,2521,2841',
  '2498,2502,2510,2521,2532,2820,2841') as isDifferent

My function was:

ALTER FUNCTION [dbo].[Redline_compareBaseProjSuccessors] (@projSuccessors varchar,@baseSuccessors varchar)
RETURNS int
AS
BEGIN
    DECLARE @proj_assignment_ids TABLE (obj_id int)
    DECLARE @base_assignment_ids TABLE (obj_id int)
    DECLARE @is_different int

    INSERT INTO @base_assignment_ids
       SELECT base_assignment_id as obj_id 
       FROM base_assignment 
       WHERE base_assignment_id IN (@baseSuccessors)

    INSERT INTO @proj_assignment_ids
       SELECT base_assignment_id AS obj_id 
       FROM proj_assignment 
       WHERE proj_assignment_id IN (@projSuccessors)

    SELECT @is_different = Count(obj_id)
    FROM @base_assignment_ids
    WHERE obj_id NOT IN (SELECT obj_id FROM @proj_assignment_ids)   

    RETURN @is_different
END

The value of @base_assignment_ids was : 2498,2502,2510,2521,2841,2532,2820 and the value of @proj_assignment_ids = 2498,2502,2510,2521,2841 so I would expect 2 as return value, but I get 0

What was wrong?

if you are creating those 2 comma separated strings it may be far more efficient to avoid them altogether and address this a different way — Paul Maxwell, Sep 26 '14 at 09:07
I'm not sure but I think your function parameters are not well defined you've set varchar and I think they should have a length i.e. varchar(100) — ADP_X, Sep 26 '14 at 09:08

score 0 · Answer 1 · answered Sep 26 '14 at 08:56

0

You can't use a string in the in operator instead of a list of values. I.e:

... in ('1, 2, 3')

is not the same as:

... in (1, 2, 3)

You either have to turn that string into a list of values, or look for the string representation of the id in the string.

The second is easy to do, but not very efficient:

insert into @base_assignment_ids
select base_assignment_id as obj_id
from base_assignment
where
  CHARINDEX(
    ',' + cast(base_assignment_id as varchar) + ',',
    ',' + @baseSuccessors + ','
  ) <> 0

answered Sep 26 '14 at 08:56

Guffa

687,336
108
737
1,005

Yes, sure I use different datatypes. I change it now to `[dbo].[Redline_compareBaseProjSuccessors] (@projSuccessors nvarchar,@baseSuccessors nvarchar)` and to `SELECT base_assignment_id as obj_id from base_assignment where base_assignment_id in ('+@baseSuccessors+')` but now it throws a `Error to convert varchar'+@baseSuccessors+' to int type` – Alexander Diedler Sep 26 '14 at 09:19
@AlexanderDiedler: It's not a difference between `varchar` and `nvarchar` that is the problem, it's that you try to use a string as SQL code. You can't use string concatenation to use a string as SQL code either. If you want to use the string in the code then you have to put the entire query in a string and then execute the string. – Guffa Sep 26 '14 at 13:35

score 0 · Answer 2 · answered Sep 26 '14 at 11:12

You need to slightly modify the insert scripts

ALTER FUNCTION [dbo].[Redline_compareBaseProjSuccessors] (@projSuccessors varchar,@baseSuccessors varchar)
RETURNS int
AS
BEGIN
    DECLARE @proj_assignment_ids TABLE (obj_id int)
    DECLARE @base_assignment_ids TABLE (obj_id int)
    DECLARE @is_different int

    INSERT INTO @base_assignment_ids
       SELECT base_assignment_id as obj_id 
       FROM base_assignment 
       WHERE base_assignment_id IN (
         SELECT  
     Split.a.value('.', 'int') AS String  
     FROM  (SELECT   
     CAST ('<M>' + REPLACE(@baseSuccessors, ',', '</M><M>') + '</M>' AS XML) AS String  
     ) AS A CROSS APPLY String.nodes ('/M') AS Split(a))

    INSERT INTO @proj_assignment_ids
       SELECT base_assignment_id AS obj_id 
       FROM proj_assignment 
       WHERE proj_assignment_id IN ( SELECT  
     Split.a.value('.', 'int') AS String  
     FROM  (SELECT   
     CAST ('<M>' + REPLACE(@projSuccessors, ',', '</M><M>') + '</M>' AS XML) AS String  
     ) AS A CROSS APPLY String.nodes ('/M') AS Split(a))

    SELECT @is_different = Count(obj_id)
    FROM @base_assignment_ids
    WHERE obj_id NOT IN (SELECT obj_id FROM @proj_assignment_ids)   

    RETURN @is_different
END

score -1 · Answer 3 · edited May 23 '17 at 11:49

I found this one Convert String to Int List but it does not work for my case.

ALTER FUNCTION [dbo].[Redline_compareBaseProjSuccessors] (@projSuccessors nvarchar(max),@baseSuccessors nvarchar(max))
RETURNS int
AS
BEGIN           
    DECLARE @proj_assignment_ids TABLE (obj_id int)
    DECLARE @base_assignment_ids TABLE (obj_id int)
    DECLARE @is_different int

    INSERT INTO @base_assignment_ids
    SELECT base_assignment_id as obj_id from base_assignment where base_assignment_id in (dbo.fnStringList2Table(@base_assignment_ids))


    INSERT INTO @proj_assignment_ids
    select base_assignment_id as obj_id from proj_assignment where proj_assignment_id in (dbo.fnStringList2Table(@proj_assignment_ids))

    SELECT @is_different=Count(obj_id)
    FROM @base_assignment_ids
    WHERE obj_id NOT IN (SELECT obj_id FROM @proj_assignment_ids)   
    RETURN @is_different
END

and the function fnStringListToTable look like this:

ALTER FUNCTION [dbo].[fnStringList2Table]
(
@List varchar(MAX)
)
RETURNS 
@ParsedList table
(
item int
)
AS
BEGIN
DECLARE @item varchar(800), @Pos int

SET @List = LTRIM(RTRIM(@List))+ ','
SET @Pos = CHARINDEX(',', @List, 1)

WHILE @Pos > 0
BEGIN
    SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
    IF @item <> ''
    BEGIN
        INSERT INTO @ParsedList (item) 
        VALUES (CAST(@item AS int))
    END
    SET @List = RIGHT(@List, LEN(@List) - @Pos)
    SET @Pos = CHARINDEX(',', @List, 1)
END

RETURN
END

But it throws (translated from German) The dbo-column or user-definied function or user-defined aggregate 'dbo.fnStringList2Table' was not found, or the username is not unique/duplicate.

That should work fine to turn the string into a list of values. From the error message it seems that the function doesn't exist. Check that you have created it, and that it ended up in the right database. — Guffa, Sep 26 '14 at 13:39

T-SQL get count of differences between two tables

3 Answers3