SQLServerCentral.com / SQL Server 2005 / Development / Alternative to using cursor / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 31 Mar 2015 15:22:35 GMT20RE: Alternative to using cursorhttp://www.sqlservercentral.com/Forums/Topic1451712-145-1.aspxNot quite sure why you are using dynamic sql in the middle of your cursor. I am very confused by the case expression in your update. You either use the value from inv_header or the value in your variable (which also came from inv_header). Since we don't have that table to work with and there is no data it is hard to know for sure but I think those two values would always be the same thing???Something like this should be a decent starting point for replacing your cursor. I may be over simplifying it but without structures to work with it is impossible to test.[code]update #inv set Phys_Inv_Date = h.call_datefrom #inv i INNER JOIN inv_header h ON i.rep_id = h.rep_id WHERE h.Phys_Inv_Date IS NULLand inv_type = 'physical'[/code]Fri, 10 May 2013 12:46:39 GMTSean LangeAlternative to using cursorhttp://www.sqlservercentral.com/Forums/Topic1451712-145-1.aspxHi,I have a table that I need to update based on the following conditions.If the inventory type is physical, use that date as the physical inventory date.Otherwise, use the date of the last physical inventory taken before this one. I'm currently using a cursor for this, but the performance of my query suffers because of it.Below are the table and queries I'm using to update it.Is there anything else I can use that's faster?Table[code="sql"]create table #inv (Rep_LName nvarchar (50),Rep_FName nvarchar (50),Rep_ID nvarchar (50),Rep_Email nvarchar (100),Rep_Status nvarchar (50),Rep_BU nvarchar (50),Sales_Force nvarchar (50),Territory nvarchar (50),Sample_Eligibility nvarchar (50),DM_Name nvarchar (100),Phys_Inv_Date datetime,Last_Reconciled datetime,Inv_Type nvarchar(50),Days_Since_Last_inv int)[/code]Queries:[code="sql"]update i set i.Inv_Type = h.inventory_type from #inv iinner join inv_header h on i.rep_id = h.rep_id and h.Call_date = (select Max(Call_Date) from inv_header i2 where i2.rep_id = i.rep_id)where i.inv_type is nulldeclare Inventory_info cursor for select distinct rep_ID, call_date, inventory_typefrom inv_headerwhere rep_id in (select rep_id from #inv)Order by rep_ID, call_date desc, inventory_type descdeclare @call_date datetimedeclare @rep_ID nvarchar(50)declare @inventory_type nvarchar(50)declare @ls_Sql as nvarchar(max)declare @param as nvarchar(max)select @ls_Sql=''select @param=''-- open cursoropen Inventory_infofetch next from Inventory_info into @rep_ID, @call_date, @inventory_typewhile (@@fetch_status = 0)begin --use parameterized dynamic sql SET @param='@rep_ID nvarchar(50),@call_date DATETIME,@inventory_type nvarchar(50)' SET @ls_Sql='update #inv set Phys_Inv_Date = case when inv_type = ''physical'' and @inventory_type = ''physical'' then @call_date else b.call_date end from #inv a INNER JOIN (select top 1 call_date, rep_id from inv_header where call_date &lt;= @call_date and rep_id = @rep_id and inventory_type = ''physical'' order by call_date desc) b ON a.rep_id = b.rep_id WHERE Phys_Inv_Date IS NULL' --pass parameter to dynamic query exec sp_executesql @ls_Sql,@param,@rep_ID,@call_date,@inventory_type fetch next from Inventory_info into @rep_ID, @call_date, @inventory_typeendclose Inventory_infodeallocate Inventory_info[/code]Fri, 10 May 2013 12:27:20 GMTjavib