Home How to turn potential loop query to set based?
Reply: 0

How to turn potential loop query to set based?

user940 Published in June 23, 2018, 9:18 pm

I have something like this currently -

declare @thisDate smalldatetime

create table #theseDates (thisDate smalldatetime)
insert into #theseDates(thisDate)
values ('11/1/2016', '5/1/2017', '9/30/2017)

create table #someData (productid int, productname varchar(32), productprice float, productsolddate smalldatetime)

declare date_cursor cursor for
select thisDate from #theseDates

open date_cursor
fetch next for date_cursor into @thisDate

while @@fetch_status = 0
   insert into #someData(soldid, productid, productname, productprice, productsolddate)
   select soldid, productid, productname, productprice, productsolddate
   from soldproducts
   where productsolddate between @thisDate and getdate()

   fetch next from date_cursor into @thisDate

close date_cursor
deallocate date_cursor

How do I change this from a cursor to a set-based solution? NOTE: added fetch line

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.30889 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO