Sorry if this is a dumb question, but I am new to SQL Server and trying a simple cursor. Works fine the first time I execute, but then @.@.Fetch_Status stays at -1 and it will not work again. If I disconnect and then connect it will work once again. What I am doing wrong?
declare @.entityid int, @.datafrom nvarchar (255),
@.datato nvarchar (255), @.updatedate datetime
declare statustime1 cursor
local scroll static
for
select audit.entityid, audit.datafrom, audit.datato, audit.updatedate
from audit
, orders
where audit.entityid = orders.orderid
and audit.entitytypeid = 4
order by orders.orderid, audit.updatedate desc
open statustime1
While @.@.fetch_status = 0
Begin
fetch next from statustime1
into @.entityid, @.datafrom, @.datato, @.updatedate
print @.entityid
end
close statustime1
deallocate statustime1
go
The main thing that you are doing 'wrong' is using a CURSOR.
SQL Server operates most efficiently handling SET based data. (NOT row-wise data). A major mistake for most developers is keeping the 'recordset' mindset.
CURSORS are considered by some to be 'evil incarnate', whereas others recognize that there is a very small and highly limited use for them.
Most likely, your operation can be handled in a single set based query, with orders of magnitude imporvements in speed and reduced blocking.
If you want to post what you are attempting to accomplish, we may be able to better direct you to a set base solution.
But the main thing to understand is that in your learning, avoid using CURSORs. Push yourself to master set based logic. You will be much happier with the results.
|||Thanks very much. I need to calculate the number of business days between two status events. Each is recorded in a row in the table, eg,
Order Event1 Date
1 Open June 1
1 Hold June 10
1Close June 30
2 Open July 1
2 hold July 10
I plan to fetch a row, save the values, fetch another row and calculate the days, continuining unitl a change in order and write output accordingly.
If you can suggest a set based approach I am all for it, however can anyone tell me why the @.@.fetch_status is not being reset.
|||There are a few 'tools' that make the DBA's job much simplier. One of those tools is have a Calendar table in the database.
With a Calendar table, it is simple to JOIN against other tables with date values and derive the span of time between such dates. You may explore using a Calendar table with this resource.
With a Calendar table, you can easily meet your requiremens with a single query.
You need a FETCH before the WHILE statement. And then normally, the FETCH inside the loop is the last line -not the first line.
|||
I actually have a time dimension table so that will be a help, but can you show how we can get the data to do the subtraction between the two dates using set based approach. The trick is bringing together two rows that follow each other in sequence for the same order. I can't figure how to JOIN to do that.
I agree on fetch first outside the loop and so on, but that does not help with the question. Why does @.@.fetch_status still have a -1 even after the close and deallocate? It only seems to reset when I disconnect and that can't be right.
Thanks,
|||You can use the function to fetch this data..
Sample.,
Code Snippet
Create function dbo.TotalBusinessDays
(
@.Start datetime,
@.End Datetime
)
returns int
as
Begin
Declare @.Result as int;
Set @.Result = 0;
While @.Start<=@.End
Begin
Select @.Result = @.Result + Case When datepart(weekday,@.Start) in (1,7) Then 0 Else 1 End
Set @.Start = @.Start + 1
End
return @.Result;
End
Go
Code Snippet
Create Table #events (
[Order] int ,
[Event1] Varchar(100) ,
[Date] datetime
);
Insert Into #events Values('1','Open','June 1 2007');
Insert Into #events Values('1','Hold','June 10 2007');
Insert Into #events Values('1','Close','June 30 2007');
Insert Into #events Values('2','Open','July 1 2007');
Insert Into #events Values('2','hold','July 10 2007');
Select
[Order]
,dbo.TotalBusinessDays([Open],[Hold]) OpenToHold
,dbo.TotalBusinessDays([Hold],[Close]) HoldToClose
From
(
Select
[Order]
, Max(case when [Event1] = 'Open' Then [Date] end) as [Open]
, Max(case when [Event1] = 'Hold' Then [Date] end) as [Hold]
, Max(case when [Event1] = 'Close' Then [Date] end) as [Close]
from
#events
Group bY
[Order]
) as Data
|||
I am impressed, looks pretty good. I may have oversimplied the example however,so how would it work for this data?
Order Event1 Date
3 Open July 1
3 Hold July 10
3 open july 20
3 hold Aug 1
3 open Aug 3
3 fill Aug 20
And I still would like to know why @.@.fetch_status stays -1 after I close and even deallocate the cursor.
|||@.@.FETCH_STATUS is similar to a static variable with connection scope.
It holds the last know value.
If you try this, in a new connection window, without a CURSOR:
SELECT @.@.FETCH_STATUS
It will return [ 0 ].
It will reset on the next execution of FETCH.
|||Bingo! You just solved it for me. I was being dense, and I was missing the point that @.@.fetch_status resets on the execution of fetch. It is not reset on open cursor. So I really need the first fetch outside the while loop and problem solved! My confusion is I am too familiar with that other DB (evil DB2) where SQLCODE would be zero after the open (lol) ..
Very interesting discussion and if you can think of set process approach to handle the more general case of multiple status changes during the order I would love to hear it.
|||
Here another sample,
Code Snippet
Create Table #orders (
[Order] int ,
[Event1] Varchar(100) ,
[Date] datetime
);
Insert Into #orders Values('3','Open','July 1 2007');
Insert Into #orders Values('3','Hold','July 10 2007');
Insert Into #orders Values('3','open','july 20 2007');
Insert Into #orders Values('3','hold','Aug 1 2007');
Insert Into #orders Values('3','open','Aug 3 2007');
Insert Into #orders Values('3','fill','Aug 20 2007');
Select * into #temp from #orders order By 1,3
Alter table #temp Add RowId int Identity(1,1)
select
[Pre].[Order],
[Pre].[Event1] +' to ' + [Post].[Event1],
dbo.TotalBusinessDays([Pre].[Date],[Post].[Date])
from
#temp [Pre]
Join #temp [Post] on [Pre].RowId = [Post].RowId-1
|||Since everyone was so nice to tell you why cursors are bad and give alternatives, and no one has answered your question:
The problem with your code is you need to change it to:
open statustime1
fetch next from statustime1
While @.@.fetch_status = 0
Begin
print @.entityid
fetch next from statustime1
into @.entityid, @.datafrom, @.datato, @.updatedate
end
In your current code you never did a fetch, so @.@.fetch_status is uninitiallized.
|||
Actually Tom, I think that I did answer that question.
Arnie Rowland wrote:
You need a FETCH before the WHILE statement. And then normally, the FETCH inside the loop is the last line -not the first line.
And
|||I am sorry Arnie, I miss that one. :< You did answer the question.
@.@.FETCH_STATUS is similar to a static variable with connection scope.
It holds the last know value.
...
It will reset on the next execution of FETCH.
