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