Loop a sql query with different value set to a variable in a query?

Question

I have a query which compares the old database with new database on Customers table who belongs to a specific department and retrieves the difference between those database tables. I have a query as below

DECLARE @departmentid int = 2001

SELECT Distinct DB.[CUSTOMER_ID],DB.[CUSTOMER_AGE]
FROM [PROD\SQL01].[PRD_Live].[dbo].[Customers] DB 
WHERE DB.[DEPARTMENT_ID]= @departmentid and 
      DB.[CUSTOMER_ID] NOT IN (SELECT Distinct [CUSTOMER_ID] 
                               FROM [NEWPRD_Live].[dbo].[Customers] 
                               WHERE [DEPARTMENT_ID]=@departmentid)

There are 40 departments id values (like 2001,2002,...,2040) that has to be set in the variable @departmentid and currently I am executing the above query by modifying the department id every time manually and executing the query for 40 times for 40 departments. Is it possible to set all the departments to a one variable and execute the query by setting each department id at a time in a loop and get all the results at a time?

Thanks Fabio for your response. No, I tried this but it is giving me wrong results because IN operator checks for all the values at the same time but i need to check for department id 2001 in old and new databases at one time and only 2002 in the next iteration and so on — user3113876, Sep 23 '19 at 07:05
You still will be able to compare values without looping them explicitly, for example `LEFT JOIN` another database results based on department id — Fabio, Sep 23 '19 at 07:23

EddiGordo · Accepted Answer · 2019-09-23T07:48:36.720

0

Try with inserting departments ID in a #temp table and use it in you where clause :

Where DEPARTMENT_ID In (Select ID From #tmpDepartment)

Edit :

Since you cannot use # tables here is a loop :

DECLARE @DEPARTMENT_ID int = 2001,
        @limit int = 2040

While @DEPARTMENT_ID <= @limit
Begin
    SELECT Distinct DB.[CUSTOMER_ID],DB.[CUSTOMER_AGE]
    FROM [PROD\SQL01].[PRD_Live].[dbo].[Customers] DB 
    WHERE DB.[DEPARTMENT_ID]= @departmentid and 
          DB.[CUSTOMER_ID] NOT IN (SELECT Distinct [CUSTOMER_ID] 
                           FROM [NEWPRD_Live].[dbo].[Customers] 
                           WHERE [DEPARTMENT_ID]=@departmentid)
   Set @DEPARTMENT_ID = @DEPARTMENT_ID + 1
End

But it is not really good design

edited Sep 23 '19 at 07:48

answered Sep 23 '19 at 06:57

EddiGordo

682
4
10

Thanks EddiGordo for your reply. I cannot do this because i dont have privileges to create any temp table as they are production databases. – user3113876 Sep 23 '19 at 07:07
Thanks EddiGordo for adding a new solution.It is good but this is helpful only when department ids are in sequence. – user3113876 Sep 23 '19 at 09:41
@user3113876 This doesn't provide the result on a single dataset as required. By the way, you can use a cursor to loop through all the departments, even when they are not in sequence. – Marc Guillot Sep 23 '19 at 09:53

Marc Guillot · Answer 2 · 2019-09-23T07:21:27.773

0

Place your IDs on a string, separated by commas, so you can use the Split String function to get all those values and use them on searching CUSTOMER_ID :

declare @ids varchar(max) = '2001,2002,2003'

SELECT Distinct DB.[DEPARTMENT_ID], DB.[CUSTOMER_ID], DB.[CUSTOMER_AGE]
FROM [PROD\SQL01].[PRD_Live].[dbo].[Customers] DB 
WHERE DB.[DEPARTMENT_ID]= (select value from string_split(@ids, ',') and 
      DB.[CUSTOMER_ID] NOT IN (SELECT Distinct [CUSTOMER_ID] 
                               FROM [NEWPRD_Live].[dbo].[Customers] 
                               WHERE [DEPARTMENT_ID]=DB.[DEPARTMENT_ID])

Note: String_split requires at least SQL Server 2016, if you are using a previous version then you must define your own Split String function, like this one T-SQL split string

edited Sep 23 '19 at 07:21

answered Sep 23 '19 at 07:04

Marc Guillot

6,090
1
15
42

Thanks @Marc Guillot for your reply. This is helpful but string split may not be required as Squirrel mentioned in solution below. – user3113876 Sep 23 '19 at 09:39
@user3113876, yes, is not needed. I mentioned it for if you want to provide those ids as a parameter instead of being hard coded on the query itself. – Marc Guillot Sep 23 '19 at 09:46

Squirrel · Answer 3 · 2019-09-23T07:17:55.037

If i understand your requirement correctly, you want the list of CUSTOMER_ID for each of the DEPARTMENT_ID. Add DEPARTMENT_ID to part of the DISTINCT and you will get the result you wanted

SELECT Distinct 
       DB.[DEPARTMENT_ID], 
       DB.[CUSTOMER_ID], 
       DB.[CUSTOMER_AGE]
FROM  [PROD\SQL01].[PRD_Live].[dbo].[Customers] DB 
where DB.[DEPARTMENT_ID] IN (2001, 2002, 2040)
and   DB.[CUSTOMER_ID] NOT IN
      (
           SELECT Distinct [CUSTOMER_ID] 
           FROM  [NEWPRD_Live].[dbo].[Customers] n
           where n.[DEPARTMENT_ID] = DB.[DEPARTMENT_ID]
      )
ORDER BY DB.[DEPARTMENT_ID], DB.[CUSTOMER_ID]

Loop a sql query with different value set to a variable in a query?

3 Answers3