SQL procedure with cursor to check if values in table 1 are also in table 2

Question

The problem is that I'm checking if result from cursor exists in CDN.ZamNag table for every iteration. Is there any better way to check if values in table 1 are also in table 2?

Time of executing below procedure takes 1 minute and 30 seconds - I'm searching for any ideas which will make executing shorter.

IF (SELECT object_id('TempDB..#temp_table')) IS NOT NULL
BEGIN
DROP TABLE #temp_table
END 

DECLARE @Seria_Nazwa VARCHAR(15)
DECLARE @Magazyn_id INT
DECLARE @Magazyn_Kod varchar(15)
DECLARE @Zlozono varchar(12)
DECLARE @dzisiaj varchar(30)
SET @dzisiaj = @data --datetime = convert(datetime,DATEADD(DAY, 0, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP)),120)
Declare @jutro datetime = convert(datetime,DATEADD(DAY, 1, @dzisiaj),120)

CREATE TABLE #temp_Table (
seria varchar(15),
magazynkod varchar(15),
zlozono varchar(5),
data varchar(30)
)   
DECLARE @get CURSOR
------------------------------------------------------
SET @get = CURSOR FOR
    SELECT Ser_Nazwa,Mag_GidNumer,Mag_Kod
    FROM CDN.Serie, CDN.Magazyny
    WHERE Mag_Kod in('1406','1407','1408')
    and SER_Nazwa like 's%' 
    and  SER_Nazwa not like 'sk%' 
    and  SER_Nazwa not like 'sa%' 
    and  SER_Nazwa not like 'st%' 
    and  SER_Nazwa not like 'su%' 
    and  SER_Nazwa not like 's14/%' 
    and  SER_Nazwa not like 's' 
    and  SER_Nazwa not like 's10' 
    and  SER_Nazwa not like 's24' 
    and  SER_Nazwa not like 's07' 
    and  SER_Nazwa not like 's28' 
    and  SER_Nazwa not like 's32' 
    and  SER_Nazwa not like 's61'

    OPEN @get
    FETCH NEXT
    FROM @get into @Seria_Nazwa,@Magazyn_Id,@Magazyn_Kod
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF (EXISTS(
        SELECT ZaN_MagNumer,Zan_ZamSeria,Zan_DataRealizacji 
        FROM CDN.ZamNag 
        WHERE 
        Zan_MagNumer = @Magazyn_Id
        and ZaN_ZamSeria = @Seria_Nazwa
        and DateAdd(day, ZaN_DataRealizacji,CONVERT(DATETIME,'1800-12-28',120)) >= @dzisiaj
        AND DateAdd(day, ZaN_DataRealizacji,CONVERT(DATETIME,'1800-12-28',120)) <  @jutro
        ))
    SET @zlozono = 'tak'
    ELSE SET @zlozono = 'nie'
    INSERT INTO #temp_Table VALUES(@Seria_Nazwa,@Magazyn_Kod,@Zlozono,@dzisiaj) 
    FETCH NEXT
    FROM @get INTO @Seria_Nazwa,@Magazyn_Id,@Magazyn_Kod
END
CLOSE @get
DEALLOCATE @get

select * from #Temp_Table
order by seria

END

Answer 1

Doing that (checking if values in Table1 are or aren't in Table2) is typically done with a LEFT JOIN. Or a RIGHT JOIN if you want to get creative. Using a CURSOR in T-SQL should be you last resort!

Something like this:

SELECT t1.[columns]
FROM [table1] t1
LEFT JOIN [table2] t2 ON t1.[key] = t2.[key]
WHERE t2.[key] IS NULL

---

Style

There are a number of accepted standards for writing T-SQL. Some people write the keywords in ALLCAPS, others write them in lowercase - which convention you chose is entirely up to you and, in the end, doesn't matter.

What does matter though, is consistency.

and DateAdd(day, ZaN_DataRealizacji,CONVERT(DATETIME,'1800-12-28',120)) >= @dzisiaj
AND DateAdd(day, ZaN_DataRealizacji,CONVERT(DATETIME,'1800-12-28',120)) <  @jutro

and

select * from #Temp_Table
order by seria

shouldn't be in the same script. Pick a convention, stick to it, and thank yourself later :)

One helpful convention I've adopted, is comma-first. This:

SELECT ZaN_MagNumer,Zan_ZamSeria,Zan_DataRealizacji 

Would look like that:

SELECT 
     ZaN_MagNumer
    ,Zan_ZamSeria
    ,Zan_DataRealizacji 

This allows you to easily comment-out, remove, add or reorder columns while reducing the room for error.

Answer 2

Cursors are a form of iterative processing that defies the design-goal of SQL to do set-based operations. As a general rule, if you need a cursor to do something in SQL, then you're doing it wrong, or your database design is not optimal.

Your query is also using a temporary table for a single select result, which is also often an indication that you're doing things wrong.

In your case, you should use a set-based join, remove the temp table, and select direct-to-output the results. The query would be something like:

with SubSeries as (

    SELECT Ser_Nazwa,Mag_GidNumer,Mag_Kod
    FROM CDN.Serie, CDN.Magazyny
    WHERE Mag_Kod in('1406','1407','1408')
    and SER_Nazwa like 's%' 
    and  SER_Nazwa not like 'sk%' 
    and  SER_Nazwa not like 'sa%' 
    and  SER_Nazwa not like 'st%' 
    and  SER_Nazwa not like 'su%' 
    and  SER_Nazwa not like 's14/%' 
    and  SER_Nazwa not like 's' 
    and  SER_Nazwa not like 's10' 
    and  SER_Nazwa not like 's24' 
    and  SER_Nazwa not like 's07' 
    and  SER_Nazwa not like 's28' 
    and  SER_Nazwa not like 's32' 
    and  SER_Nazwa not like 's61'

), SubZamNag as (

    SELECT ZaN_MagNumer,Zan_ZamSeria,Zan_DataRealizacji 
    FROM CDN.ZamNag 
    WHERE 
        DateAdd(day, ZaN_DataRealizacji,CONVERT(DATETIME,'1800-12-28',120)) >= @dzisiaj
    AND DateAdd(day, ZaN_DataRealizacji,CONVERT(DATETIME,'1800-12-28',120)) <  @jutro

)
SELECT Ser_Nazwa as seria,
       Mag_Kod as magazynkod,
       case when exists(select 1
                        from SubZamNag
                        where Zan_MagNumer = Mag_GidNumer
                          and ZaN_ZamSeria = Ser_Nazwa
                        )
            then 'tak'
            else 'nie'
       end as zlozono,
       @dzisiaj as data
from SubSeries

Note the use of the Common Table Expression (CTE) to reduce the complexity of the final select.

There is no cursor needed, no temp table, and the operation is set-based.