CURSORS!!!!! UGH. Please Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CURSORS!!!!! UGH. Please Help

Author

Topic

springer13
Starting Member

3 Posts

Posted - 2008-01-07 : 19:07:04

I am trying to write a stored procedure that will insert rows of data meeting my criteria using an inserted stored procedure that should update each row with a unique id. However, when I run it, all of the rows have the same id. I'm new to cursors so any help is appreciated!!!

CREATE PROCEDURE dbo.usp_CreateWelcomeCallTickets AS
DECLARE @ActivityID char(12),
@Accountid char(12),
@Createdate DateTime,
@Createuser char(12),
@Modifydate DateTime,
@Modifyuser char(12),
@Contactid char(12),
@Type int,
@Category varchar(64),
@Description varchar(64),
@Phonenumber varchar(32),
@UserID char(12),
@Notes varchar(255),
@Longnotes varchar(255),
@UserDef2 varchar(255),
@Ticketnumber varchar(64)

--Select accountid, Left(homephone ,3) as areacode
Select accountid, Left(homephone ,3) as areacode
into #temp
From sysdba.contact
--Create Activity
DECLARE C1 CURSOR FOR
SELECT A.AccountID, GetUTCDate() as CreateDate,
'ADMIN' as CreateUser, GetUTCDate() as ModifyDate,
'Admin' as ModifyUser, C.ContactID, '262146' as [Type],
'Welcome Call 1' as Category,
'Welcome Call 20 Queue – 02/01' as Description, C.HomePhone as PhoneNumber,
'U6UJ6A0001WB' as userid, 'Welcome Call' as Notes,
'Welcome Call' as LongNotes, 'WC' as userdef2,
ac.timezonecode AS TicketNumber
from sysdba.ACX_CHECKLIST cl
join sysdba.account a on a.accountid = cl.accountid
join #temp t on t.accountid = a.accountid
join sysdba.areacodexref ac on CAST(ac.areacode AS varchar) = CAST(t.areacode AS Varchar)
JOIN sysdba.contact c on c.accountid = a.accountID
where cl.SERVFEESCHEDULEDDATE between '12/1/2007' and '1/1/2008'

Open C1
--The stored procedure that puts unique codes in
EXEC sysdba.usp_GetNextKeyNested 5, '6UJ6', @ActivityID OUTPUT
INSERT INTO sysdba.activity (
ActivityID,
Accountid,
Createdate,
Createuser,
Modifydate,
Modifyuser,
Contactid,
[Type],
Category,
Description,
Phonenumber,
UserID,
Notes,
Longnotes,
UserDef2,
Ticketnumber)
VALUES (
@ActivityID,
@AccountID,
@Createdate,
@Createuser,
@Modifydate,
@Modifyuser,
@Contactid,
@Type,
@Category,
@Description,
@Phonenumber,
@UserID,
@Notes,
@Longnotes,
@UserDef2,
@Ticketnumber)
FETCH NEXT FROM c1
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM C1

END
CLOSE C1
DEALLOCATE C1

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-07 : 19:11:15

Is @ActivityID the unique ID that you are trying to retrieve for each loop and insert into the activity table?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

springer13
Starting Member

3 Posts

Posted - 2008-01-07 : 22:52:57

Yes @ActivityID is the unique item that will be completed for each row using the stored procedure EXEC sysdba.usp_GetNextKeyNested 5, '6UJ6', @ActivityID OUTPUT.

Thanks for your help!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-07 : 23:02:56

Can you explain your requirement in more detail? i feel this can be done in a much simpler way.

springer13
Starting Member

3 Posts

Posted - 2008-01-08 : 09:13:31

ok, I am updating an existing table with new activities/rows with certain default values that are contained in the select statement for the month of December. Each row should have a unique Activity ID. If I generate the Select statement the data is correct, however when I add the Exec Activity statement it puts the same Activity ID on all of the rows, which won't work. So, I somehow need to make it loop to create a new activity id for each activity/row in the date range. I hope this helps..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-08 : 09:38:37

Cant you use WHILE loop for lopping through?

like

CREATE PROCEDURE dbo.usp_CreateWelcomeCallTickets AS
DECLARE @ActivityID char(12),
@Accountid char(12

--Select accountid, Left(homephone ,3) as areacode
Select accountid, Left(homephone ,3) as areacode
into #temp
From sysdba.contact

SET @AccountID=MIN(accountid)
FROM #temp 

WHILE @AccountID IS NOT NULL
BEGIN

--The stored procedure that puts unique codes in
EXEC sysdba.usp_GetNextKeyNested 5, '6UJ6', @ActivityID OUTPUT 

--Create Activity
INSERT INTO sysdba.activity (
ActivityID,
Accountid,
Createdate,
Createuser,
Modifydate,
Modifyuser,
Contactid,
[Type],
Category,
Description,
Phonenumber,
UserID,
Notes,
Longnotes,
UserDef2,
Ticketnumber)
SELECT @ActivityID,
A.AccountID, 
GetUTCDate() as CreateDate, 
'ADMIN' as CreateUser, 
GetUTCDate() as ModifyDate, 
'Admin' as ModifyUser, 
C.ContactID, 
'262146' as [Type], 
'Welcome Call 1' as Category, 
'Welcome Call 20 Queue – 02/01' as Description, 
C.HomePhone as PhoneNumber,
'U6UJ6A0001WB' as userid, 
'Welcome Call' as Notes, 
'Welcome Call' as LongNotes, 
'WC' as userdef2, 
ac.timezonecode AS TicketNumber
from sysdba.ACX_CHECKLIST cl
join sysdba.account a on a.accountid = cl.accountid
join #temp t on t.accountid = a.accountid
join sysdba.areacodexref ac on CAST(ac.areacode AS varchar) = CAST(t.areacode AS Varchar)
JOIN sysdba.contact c on c.accountid = a.accountID
where cl.SERVFEESCHEDULEDDATE between '12/1/2007' and '1/1/2008'
AND A.AccountID=@AccountID

SET @AccountID=MIN(accountid)
FROM #temp 
WHERE accountid >@AccountID
END

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-08 : 11:51:12

Why is @ActivityID declared as CHAR(12)?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources