July 27, 2009 at 2:53 pm
I don't have a connection to a DB to test it.
Does the syntax look correct? Thanks.
DECLARE confreghistcursor CURSOR
FOR
SELECT r.createdbyname, r.pa_registrationsetupidname, @wksOut AS weeksOut , d.extendedAmount
FROM eventregistration r
INNER JOIN invoicedetail d
ON r.pa_invoicedetailid=d.invoicedetailid
WHERE r.pa_meetingsetupid=@pa_eventmeetingsetupid
AND r.createdon BETWEEN DATEADD(WEEK,@wksOut,@pa_meetingstartdate) AND DATEADD(WEEK, @wksOut-1,@pa_meetingstartdate)
OPEN confreghistcursor
FETCH NEXT FROM confreghistcursor INTO @createdbyname, @registrationsetupidname, @weeksout, @extendedamount
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO war_ConferenceRegistations VALUES (@createdbyname, @registrationsetupidname, @weeksout, @extendedamount)
END
CLOSE confreghistcursor
DEALLOCATE confreghistcursor
July 27, 2009 at 2:57 pm
Why are you using a cursor for this? It's a straightforward insert ... select
This will do the same as your cursor and very likely much faster.
INSERT INTO war_ConferenceRegistations
SELECT r.createdbyname, r.pa_registrationsetupidname, @wksOut, d.extendedAmount
FROM eventregistration r
INNER JOIN invoicedetail d ON r.pa_invoicedetailid=d.invoicedetailid
WHERE r.pa_meetingsetupid=@pa_eventmeetingsetupid
AND r.createdon BETWEEN DATEADD(WEEK,@wksOut,@pa_meetingstartdate) AND DATEADD(WEEK, @wksOut-1,@pa_meetingstartdate)
Just beware the upper bound on that between. Betweens are inclusive on both sides
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 27, 2009 at 3:00 pm
I've read cursors are slow. It's not for a production type environment.
I'm just trying to learn how to create cursors since I'm not familar with them.
Honestly I'm not sure why someone would use a cursor. Need some good resources on learning more about them.
July 27, 2009 at 3:16 pm
Ah, in that case, your syntax for the cursor is almost right, you just need another FETCH within the Begin.. end of the while loop.
Pretty much the only thing I use cursors for are for admin tasks that have to be done one by one. Database backups, database consistency checks, index rebuilds, that kind of thing. They're also places where the overhead of the cursor becomes relatively insignificant against the time of the backup/index rebuild/etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 28, 2009 at 5:18 am
You are almost there very good first attempt. you are missing only one line.
use follwoing template
DECLARE confreghistcursor CURSOR
FOR
SELECT
OPEN confreghistcursor
FETCH NEXT FROM confreghistcursor INTO @createdbyname, @registrationsetupidname, @weeksout, @extendedamount
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO war_ConferenceRegistations
--------------- Missing statement
FETCH NEXT FROM confreghistcursor INTO @createdbyname, @registrationsetupidname, @weeksout, @extendedamount
--------------- Missing statement
END
CLOSE confreghistcursor
DEALLOCATE confreghistcursor
July 28, 2009 at 7:32 am
Thanks guys.
I'm just curious why another fetch is needed. I tested your suggestion and it worked.
July 28, 2009 at 8:15 am
It is possible to do a cursor loop with only a single fetch:
declare Cur_Cursor cursor local
for
select
MyData
from
MyTable a
order by
a.MyData
open Cur_Cursor
declare @MyData varchar(100)
while 1=1
begin
fetch next from Cur_Cursor into @MyData
if @@fetch_status 0 break
/* rest of loop processing here */
end --While end
close Cur_Cursor
deallocate Cur_Cursor
July 28, 2009 at 8:26 am
Iron Chef SQL (7/28/2009)
I'm just curious why another fetch is needed. I tested your suggestion and it worked.
There has to be a fetch inside the while loop to get the next row and the next and the next... Without one inside the loop, the while will run forever and repeatedly process the same row.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 28, 2009 at 8:30 am
OOOOH okay. that makes perfect sense now.
I was thinking the first one was all I needed but it makes sense now because the second FETCH is inside the BEGIN END block.
Is it possible to nest a cursor inside a cursor, if so why would anyone do so.
Thanks again for educating me lol. 😀
July 28, 2009 at 8:34 am
Yes it is possible. I would not suggest it.
July 28, 2009 at 8:35 am
Iron Chef SQL (7/28/2009)
Is it possible to nest a cursor inside a cursor,
Yes.
if so why would anyone do so.
Good question...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 28, 2009 at 9:39 am
GilaMonster (7/27/2009)
Ah, in that case, your syntax for the cursor is almost right, you just need another FETCH within the Begin.. end of the while loop.Pretty much the only thing I use cursors for are for admin tasks that have to be done one by one. Database backups, database consistency checks, index rebuilds, that kind of thing. They're also places where the overhead of the cursor becomes relatively insignificant against the time of the backup/index rebuild/etc.
Consistency checks and index rebuilds I'd agree with... but apparently executing multiple backups seperated by ;'s will work too 🙂
July 28, 2009 at 9:48 am
mtassin (7/28/2009)
Consistency checks and index rebuilds I'd agree with... but apparently executing multiple backups seperated by ;'s will work too 🙂
Depends how much logic you're building into your backup routine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 28, 2009 at 11:24 am
Pretty Neat 😎
CREATE PROCEDURE usp_CONF_reg_historicals
/*==============================================================================================
IRON CHEF
Today's secret ingredient: Cursors
==============================================================================================*/
AS
BEGIN
DECLARE @pa_eventmeetingsetupid NVARCHAR(38)
DECLARE @pa_eventidname NVARCHAR(55)
DECLARE @pa_meetingstartdate DATETIME
DECLARE @CT INT
DECLARE @createdbyname NVARCHAR(55)
DECLARE @registrationsetupidname NVARCHAR(55)
DECLARE @extendedamount MONEY
DELETE FROM ironchef_database_user.dbo.war_ConferenceRegistrations
-- meetings
DECLARE eventCursor CURSOR FOR
SELECT /*TOP 3*/ pa_eventmeetingsetupid,pa_eventidname, pa_meetingstartdate
FROM ironchef_database.dbo.FilteredPA_EventMeetingSetup
WHERE ironchef_broadcategory 2
OPEN eventCursor
FETCH NEXT FROM eventCursor INTO @pa_eventmeetingsetupid, @pa_eventidname , @pa_meetingstartdate
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @pa_eventidname
-- 12 weeks
DECLARE wkOutCursor CURSOR FOR
SELECT ct
FROM ironchef_database_user.dbo.user_countTo52
WHERE ct <=12
ORDER BY ct
OPEN wkOutCursor
FETCH NEXT FROM wkOutCursor into @CT
WHILE @@FETCH_STATUS=0
BEGIN
-- get week out data
PRINT ' weeks out ' + cast(@ct as nvarchar(5))
DECLARE confreghistcursor CURSOR FOR
SELECT r.createdbyname, r.pa_registrationsetupidname, @CT AS weeksOut , d.extendedAmount
FROM ironchef_database.dbo.FilteredPA_EventRegistration r
INNER JOIN ironchef_database.dbo.FilteredInvoiceDetail d
ON r.pa_invoicedetailid=d.invoicedetailid
WHERE r.pa_meetingsetupid=@pa_eventmeetingsetupid
AND r.createdon BETWEEN DATEADD(WEEK,-@ct,@pa_meetingstartdate) AND DATEADD(WEEK, -@ct+1,@pa_meetingstartdate)
OPEN confreghistcursor
FETCH NEXT FROM confreghistcursor INTO @createdbyname, @registrationsetupidname, @CT, @extendedamount
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @pa_eventidname + ' ' + @registrationsetupidname
INSERT INTO ironchef_database_user.dbo.war_ConferenceRegistrations VALUES (@pa_eventmeetingsetupid, @pa_eventidname, @createdbyname, @registrationsetupidname, @CT, @extendedamount)
FETCH NEXT FROM confreghistcursor INTO @createdbyname, @registrationsetupidname, @CT, @extendedamount
END
CLOSE confreghistcursor
DEALLOCATE confreghistcursor
FETCH NEXT FROM wkOutCursor into @CT
END
CLOSE wkOutCursor
DEALLOCATE wkOutCursor
FETCH NEXT FROM eventCursor INTO @pa_eventmeetingsetupid, @pa_eventidname , @pa_meetingstartdate
END
CLOSE eventCursor
DEALLOCATE eventCursor
END
July 28, 2009 at 11:24 am
how did you insert that white box with the sql code?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply