SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Alternative to using cursor


Alternative to using cursor

Author
Message
javib
javib
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 17
Hi,

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
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)



Queries:

update i 
set i.Inv_Type = h.inventory_type
from #inv i
inner 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 null

declare Inventory_info cursor
for select distinct rep_ID, call_date, inventory_type
from inv_header
where rep_id in (select rep_id from #inv)
Order by rep_ID, call_date desc, inventory_type desc
declare @call_date datetime
declare @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 cursor
open Inventory_info

fetch next from Inventory_info
into @rep_ID, @call_date, @inventory_type
while (@@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 <= @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_type
end

close Inventory_info
deallocate Inventory_info


Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26020 Visits: 17528
Not 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.


update #inv
set Phys_Inv_Date = h.call_date
from #inv i
INNER JOIN inv_header h ON i.rep_id = h.rep_id
WHERE h.Phys_Inv_Date IS NULL
and inv_type = 'physical'



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search