SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 CURSOR w\multiple tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Chris_Kelley
Posting Yak Master

106 Posts

Posted - 08/22/2014 :  09:04:14  Show Profile  Reply with Quote
Hi everyone.

I have been tasked to replicate notes for one table across all linked accounts. Basically what I have now is a temp table to grab all the notes connected to a user, then a temp table to grab all the connected accounts to that original user, then what I am hoping a cursor to insert all the notes into the connected users. (sorry that's a lot of users)

here is the code that I have, will this work? better ideas?


CREATE PROCEDURE dbo.proc_CUSTOM86
@DEBT_KEY NUMERIC(9,0),
@USER_KEY NUMERIC(9,0),
@DATE DATETIME
AS
DECLARE @TRANS_K INTEGER,
@DEBTOR_KEY NUMERIC(9,0),
@NOTE CHAR(200)

--========================================--
-- Assign values to constants and variables.
--========================================--
SET NOCOUNT ON
SET @TRANS_K = @@TRANCOUNT

CREATE TABLE #STAGING_NOTE
(
NOTE_KEY NUMERIC(9,0),
DEBOTR_KEY NUMERIC(9,0),
DEBT_KEY NUMERIC(9,0),
USER_KEY NUMERIC(5,0),
NOTE_DATE DATETIME,
NOTE_TIME CHAR(8),
NOTE_TEXT CHAR(80)
)
----------------------------------------------------
INSERT INTO #STAGING_NOTE
SELECT NOTE_KEY,
DEBTOR_KEY,
DEBT_KEY,
USER_KEY,
NOTE_DATE,
NOTE_TIME,
NOTE_TEXT
FROM NOTE WHERE DEBT_KEY = @DEBT_KEY
-----------------------------------------------------
-----------------------------------------------------
CREATE TABLE #LINKED_ACCOUNT
(
DEBT_KEY NUMERIC(9,0)
)
-----------------------------------------------------
INSERT INTO #LINKED_ACCOUNT
-----------------------------------------------------------------------------
--ALL ACCOUNTS (428 - Replicate Notes-All Accts)
SELECT DEBT_KEY FROM LINK_ACCT WITH (NOLOCK) WHERE LINK_REF =
(SELECT LINK_REF FROM LINK_ACCT WITH (NOLOCK) WHERE DEBT_KEY = @DEBT_KEY)
--
DELETE FROM #LINKED_ACCOUNT WHERE DEBT_KEY = @DEBT_KEY
-----------------------------------------------------------------------------
DECLARE NOTE_CURS CURSOR LOCAL STATIC FORWARD_ONLY
FOR
SELECT (SELECT TOP 1 DEBT_KEY FROM #LINKED_ACCOUNT), DEBTOR_KEY, USER_KEY, NOTE_TEXT
FROM #STAGING_NOTE

OPEN NOTE_CURS

WHILE 1=1
BEGIN
FETCH NEXT FROM NOTE_CURS
INTO @DEBT_KEY, @DEBTOR_KEY, @USER_KEY, @NOTE

IF @@FETCH_STATUS <> 0
BREAK
EXEC proc_TOSS_NOTE @DEBT_KEY, @DEBTOR_KEY, @USER_KEY, @NOTE
DELETE TOP (1) FROM #LINKED_ACCOUNT
END

CLOSE NOTE_CURS
DEALLOCATE NOTE_CURS

DROP TABLE #STAGING_NOTE
DROP TABLE #LINKED_ACCOUNT







Thanks,
Chris
Jr Programmer

Chris_Kelley
Posting Yak Master

106 Posts

Posted - 08/22/2014 :  09:10:02  Show Profile  Reply with Quote
I am pretty sure this isn't going to work, I could have 5 notes I need to replicate across the other accounts I am not sure if I need a cursor inside a cursor or I need to merge the temp tables together then into the cursor......

Thanks,
Chris
Jr Programmer
Go to Top of Page

Chris_Kelley
Posting Yak Master

106 Posts

Posted - 08/22/2014 :  09:34:36  Show Profile  Reply with Quote
I added another cursor and that seems to work, sorry for the interruption.

--********************************************************************************--
--************************ PROCEDURE - proc_CUSTOM86 *************************--
--********************************************************************************--
IF OBJECT_ID('dbo.proc_CUSTOM86') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.proc_CUSTOM86
PRINT 'Replaced Stored Procedure: dbo.proc_CUSTOM86'
END
ELSE
BEGIN
PRINT 'Added New Stored Procedure: dbo.proc_CUSTOM86'
END
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING OFF
GO
--********************************************************************************--
-- DESCRIPTION: Result code procs that will replicate notes entered the current
-- DESCRIPTION: account across a linked active accounts, and another for all
-- DESCRIPTION: accounts result codes are
-- DESCRIPTION: 428 - Replicate Notes-All Accts
-- CREATE TASK: #862
-- CREATE DATE: 08/20/2014
-- DEVELOPER: Chris Kelley
------------------------------------------------------------------------------------
-- UPDATE NOTE:
-- UPDATE TASK:
-- UPDATE DATE:
-- DEVELOPER:
--********************************************************************************--
CREATE PROCEDURE dbo.proc_CUSTOM86
@DEBT_KEY NUMERIC(9,0),
@USER_KEY NUMERIC(9,0),
@DATE DATETIME
AS
DECLARE @TRANS_K INTEGER,
@DEBTOR_KEY NUMERIC(9,0),
@NOTE CHAR(200),
@NOTE_KEY NUMERIC(9,0),
@NOTE_DATE DATETIME,
@NOTE_TIME VARCHAR(8)

--========================================--
-- Assign values to constants and variables.
--========================================--
SET NOCOUNT ON
SET @TRANS_K = @@TRANCOUNT

CREATE TABLE #STAGING_NOTE
(
NOTE_KEY NUMERIC(9,0),
DEBOTR_KEY NUMERIC(9,0),
DEBT_KEY NUMERIC(9,0),
USER_KEY NUMERIC(5,0),
NOTE_DATE DATETIME,
NOTE_TIME CHAR(8),
NOTE_TEXT CHAR(80)
)
----------------------------------------------------
INSERT INTO #STAGING_NOTE
SELECT NOTE_KEY,
DEBTOR_KEY,
DEBT_KEY,
USER_KEY,
NOTE_DATE,
NOTE_TIME,
NOTE_TEXT
FROM NOTE WHERE DEBT_KEY = @DEBT_KEY
-----------------------------------------------------
-----------------------------------------------------
CREATE TABLE #LINKED_ACCOUNT
(
DEBT_KEY NUMERIC(9,0)
)
-----------------------------------------------------
INSERT INTO #LINKED_ACCOUNT
-----------------------------------------------------------------------------
--ALL ACCOUNTS (428 - Replicate Notes-All Accts)
SELECT DEBT_KEY FROM LINK_ACCT WITH (NOLOCK) WHERE LINK_REF =
(SELECT LINK_REF FROM LINK_ACCT WITH (NOLOCK) WHERE DEBT_KEY = @DEBT_KEY)
--
DELETE FROM #LINKED_ACCOUNT WHERE DEBT_KEY = @DEBT_KEY
-----------------------------------------------------------------------------
DECLARE ACCT_CURS CURSOR LOCAL STATIC FORWARD_ONLY
FOR
SELECT DEBT_KEY
FROM #linked_account

OPEN ACCT_CURS

WHILE 1=1
BEGIN
FETCH NEXT FROM ACCT_CURS
INTO @DEBT_KEY

IF @@FETCH_STATUS <> 0
BREAK

SELECT @DEBTOR_KEY = DEBTOR_KEY FROM DEBT WHERE DEBT_KEY = @DEBT_KEY

DECLARE NOTE_CURS CURSOR LOCAL STATIC FORWARD_ONLY
FOR
SELECT NOTE_DATE, USER_KEY, NOTE_TEXT
FROM #STAGING_NOTE

OPEN NOTE_CURS
WHILE 1 = 1
BEGIN

FETCH NEXT FROM NOTE_CURS INTO @NOTE_DATE,@USER_KEY,@NOTE_TEXT

EXEC proc_KEY_CONTROL_upd 'NOTE', @NOTE_KEY OUTPUT

INSERT NOTE (NOTE_KEY,DEBTOR_KEY, DEBT_KEY, USER_KEY, NOTE_DATE,NOTE_TIME,NOTE_TEXT)
VALUES (@NOTE_KEY, @DEBTOR_KEY,@DEBT_KEY,@USER_KEY,@NOTE_DATE,@NOTE_TIME,@NOTE)

END
CLOSE NOTE_CURS
DEALLOCATE NOTE_CURS

END

CLOSE ACCT_CURS
DEALLOCATE ACCT_CURS

DROP TABLE #STAGING_NOTE
DROP TABLE #LINKED_ACCOUNT

--========================================--
-- Clean up and Exit
--========================================--
SET NOCOUNT OFF
RETURN 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
GRANT EXECUTE ON dbo.proc_CUSTOM86 TO PUBLIC
GO
EXECUTE sp_addextendedproperty 'Caption', 'LAST UPDATE: ', 'USER', dbo, 'PROCEDURE', proc_CUSTOM86, NULL, NULL
GO
EXECUTE sp_addextendedproperty 'Developer', 'LAST UPDATED BY: ', 'USER', dbo, 'PROCEDURE', proc_CUSTOM86, NULL, NULL
GO
EXECUTE sp_addextendedproperty 'Description', 'Result code procs that will replicate notes', 'USER', dbo, 'PROCEDURE', proc_CUSTOM86, NULL, NULL
GO



Thanks,
Chris
Jr Programmer
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1116 Posts

Posted - 08/22/2014 :  09:50:42  Show Profile  Reply with Quote
I cannot see why you are using a cursor at all. I don't believe it is needed for this problem. (In fact, I don't believe a cursor is ever needed. I'm still looking for a problem that cannot be solved with set-based logic.)

What is proc_TOSS_NOTE doing?
Go to Top of Page

Chris_Kelley
Posting Yak Master

106 Posts

Posted - 08/23/2014 :  17:28:27  Show Profile  Reply with Quote
well, that's what all the programmers at my shop use.. I am currently watching videos that's are against cursors and advocate for a declare set solution.

the toss note proc just tosses a note into the database.

https://www.youtube.com/watch?v=IRZ8VA2C9g0&index=4&list=PLS-P4cIi0lXMLiGfBPM9ur1mkrKCJTMjG

Thanks,
Chris
Jr Programmer
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1116 Posts

Posted - 08/24/2014 :  08:52:23  Show Profile  Reply with Quote
Cursors should be avoided at all costs. If the toss_note process just does an insert, I'd pull the logic into your new proc which can be entirely set based.
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1116 Posts

Posted - 08/24/2014 :  08:52:29  Show Profile  Reply with Quote
Cursors should be avoided at all costs. If the toss_note process just does an insert, I'd pull the logic into your new proc which can be entirely set based.
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1116 Posts

Posted - 08/24/2014 :  08:52:30  Show Profile  Reply with Quote
Cursors should be avoided at all costs. If the toss_note process just does an insert, I'd pull the logic into your new proc which can be entirely set based.
Go to Top of Page

Chris_Kelley
Posting Yak Master

106 Posts

Posted - 08/24/2014 :  21:34:32  Show Profile  Reply with Quote
Thanks for the advice....I am currently learning so I appreciate the insight.

Thanks,
Chris
Jr Programmer
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000