please optimize the following query.i want to remove while
please optimize the following query.
i want to remove while loop
CREATE TABLE #WebsiteRestrictionCarName
(
RestrictionId int identity(1,1) primary key,
WebsiteId int,
WsCarName nvarchar(100),
TransmissionId int,
SIPPcode nvarchar(5)
)
CREATE TABLE #ReportDetail
(
Id int identity(1,1) primary key,
WebsiteId int,
CarName nvarchar(100),
TransmissionId int,
FinalSIPPCode nvarchar(50),
oldSIPPcode nvarchar(50)
)
insert into #WebsiteRestrictionCarName values (106,'Citroen%C4%Picasso',NULL,'IVMR')
insert into #WebsiteRestrictionCarName values (106,'Citroen%C4',1,'CDAR')
insert into #WebsiteRestrictionCarName values (106,'Citroen%C4',2,'CDMR')
select * from #WebsiteRestrictionCarName
insert into #ReportDetail
select 106,'Citroen C4 Picasso or similar',2,NULL,NULL
Declare @TokenCount int, @TokenIndex int,
@WebsiteId int, @WsCarName nvarchar(500),
@TransmissionId int , @SIPPcode nvarchar(100)
SELECT @TokenCount = COUNT('x'), @TokenIndex = 1 FROM #WebsiteRestrictionCarName WITH(NOLOCK)
WHILE (@TokenCount >= @TokenIndex)
BEGIN
SELECT @WebsiteId = WebsiteId, @WsCarName = WsCarName,
@TransmissionId = TransmissionId, @SIPPcode = SIPPcode
FROM #WebsiteRestrictionCarName WITH(NOLOCK)
WHERE RestrictionId = @TokenIndex
Print @WsCarName + ' | ' + @SIPPcode
IF (@TransmissionId IS NOT NULL)
--with Transmission
UPDATE RD
SET RD.oldSIPPcode = @SIPPcode
FROM #ReportDetail RD WITH(NOLOCK)
WHERE RD.CarName like '%'+ @WsCarName +'%'
AND RD.TransmissionId = @TransmissionId
AND RD.WebsiteId = @WebsiteId
AND RD.CarName IS NOT NULL
AND RD.oldSIPPcode IS NULL
ELSE
--without Transmission
UPDATE RD
SET RD.oldSIPPcode = @SIPPcode
FROM #ReportDetail RD WITH(NOLOCK)
WHERE RD.CarName like '%'+ @WsCarName +'%'
AND RD.WebsiteId = @WebsiteId
AND RD.CarName IS NOT NULL
AND RD.oldSIPPcode IS NULL
SET @TokenIndex = @TokenIndex + 1
END
select * from #ReportDetail