Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Alternative to using cursor Expand / Collapse
Author
Message
Posted Friday, May 10, 2013 12:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 9:05 AM
Points: 4, 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

Post #1451712
Posted Friday, May 10, 2013 12:46 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:05 PM
Points: 13,327, Visits: 12,820
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 Moden's 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)
Post #1451715
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse