CODEWITHSUNDEEP
sqlsql-servert-sql
StepUp
StepUp

Reputation: 38144

Find id by comparing query results

I have a table variable Names which contains the following data: 

SELECT * FROM Names

and its result:

Name
-----
Jon
Adam 
Ben
Joseph

In addition, there is a function fn_GetNamesById(@Id) which gets Name by id(1):

SELECT * FROM fn_GetColumns(1)

and its result would be: 

Name
-----
Jon
Adam 
Ben
Joseph

another id(2) would return another result 

SELECT * FROM fn_GetColumns(2)

and its result would be: 

Name
-----
Adam 
Jon
Joseph
Ben

another id(3) would return another result: 

SELECT * FROM fn_GetColumns(3)

and its result would be: 

Name
-----
Marc
William 
Gordon
Wiktor
Felix

What I want is to find id in UDF/Stored procedure named Find_ID(@IDs, @Names) by comparing result of table variable Names with result of function fn_GetColumns(id).

The parameters of Stored Procedure/UDF are:

@IDs TABLE(ID uniqueidentifier) -- possible IDs (1,2,3, ...1000)
@Names TABLE(ID uniqueidentifier) -- desired names

To do this, I've written a stored procedure Find_ID which contains a loop WHILE to iterate through IDs. Current id will be put in function fn_GetColumns(id) and then the result of fn_GetColumns(id) will be compared with result table variable @Names.

However, I do not know how I can compare two query result and get current id if query result are the same: 

-- My stored procedure
CREATE PROCEDURE [Find_ID]   (
@IDs [UWQ].[TY_MyType] READONLY,
@Names [UWQ].[TY_MyNames] READONLY

DECLARE @IDs TABLE(ID uniqueidentifier)
DECLARE @Names TABLE(ID uniqueidentifier)
DECLARE @Processed INT


DECLARE @COUNTER INT = 0;
DECLARE @MAX INT = (SELECT COUNT(*) FROM @IDs)
DECLARE @VALUE VARCHAR(50);

--loop:
WHILE @COUNTER < @MAX
BEGIN

    --we are iterating through id = 1, 2, 3
    SET @VALUE = (SELECT ID FROM
        (SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) [index] , 
            ID from @IDs) R 
         ORDER BY R.[index] OFFSET @COUNTER 
         ROWS FETCH NEXT 1 ROWS ONLY);

    // pseudo code: if  SELECT * FROM fn_GetColumns(1) is equal to SELECT * 
    // FROM Names THEN return @VALUE
    // pseudo code: else iterate to find id 
    // pseudo code: if there is no names then return NULL

    SET @COUNTER = @COUNTER + 1

END

Is it possible to find id of function fn_GetColumns(id) if query results are the same?

For example:

DECLARE @Names table 
(
    Names VARCHAR(50)
)
insert into @Names 
VALUES
    (Jon), (Adam) , (Ben), (Joseph)

DECLARE @Ids table 
(
    ID int
)
insert into @Ids 
VALUES
    (1),(2) , (3), (4)

EXEC Find_ID(@IDs, 
         @Names)

OUTPUT: 1 -- as UDF fn_GetColumns(1) returns the same query result
like SELECT * FROM Names

Upvotes: 0

Views: 369

Answers (2)

StepUp
StepUp

Reputation: 38144

I realized that I can just count of query result of EXCEPT operator. If count of unique rows is 0, then it means that necessary ID is found:

 SELECT 
     @DesiredID = count(1) 
 FROM 
 (
     SELECT * FROM (SELECT * FROM @Names) InputNames
     EXCEPT
     SELECT * FROM (SELECT TOP 1000 Name 
     FROM fn_GetColumns(@VALUE) ORDER BY ColumnOrder) DesiredNames
 ) rn

 IF @DesiredID = 0
 BEGIN
     PRINT @DesiredID
     PRINT @VALUE
     BREAK
 END

The whole code looks like this:

DECLARE @IDs TABLE(ID uniqueidentifier)
DECLARE @Processed INT
INSERT INTO @IDs
SELECT 
f.ID 
FROM Fields f
GROUP BY f.ID

DECLARE @Names TABLE
(
    Name varchar(50)
)

INSERT INTO @Names
VALUES
('Jon'), ('Adam'), ('Ben'), ('Joseph')



 DECLARE @COUNTER INT = 0;
 DECLARE @MAX INT = (SELECT COUNT(*) FROM @IDs)
 DECLARE @VALUE VARCHAR(50);
 DECLARE @DesiredID VARCHAR(50);

 WHILE @COUNTER < @MAX
 BEGIN

     SET @VALUE = (SELECT ID FROM
         (SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) [index] , ID from @IDs) R 
          ORDER BY R.[index] OFFSET @COUNTER 
          ROWS FETCH NEXT 1 ROWS ONLY);

          --PRINT @VALUE
         SELECT 
             @DesiredID = count(1) 
         FROM 
         (
             SELECT * FROM (SELECT * FROM @Names) InputNames
             EXCEPT
             SELECT * FROM (SELECT TOP 1000 Name 
             FROM fn_GetColumns(@VALUE) ORDER BY ColumnOrder) DesiredNames
         ) rn

         IF @DesiredID = 0
         BEGIN
             PRINT @DesiredID
             PRINT @VALUE
         BREAK
     END

     SET @COUNTER = @COUNTER + 1

END

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270301

Assuming the names are different, you can do:

SELECT (CASE WHEN COUNT(*) = nn.cnt THEN 1 ELSE 0 END) as all_same
FROM Names n JOIN
     fn_GetColumns(@id) gc(name)
     ON n.name = gc.name CROSS JOIN
     (SELECT COUNT(*) as cnt FROM names) nn;

This counts the number of matches and compares it to the total number of names.

Upvotes: 1

Related Questions