Update Script Help - SQL 2005 - Cursor

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)

Update Script Help - SQL 2005 - Cursor

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2009-03-17 : 13:15:35

I need help with my update script below.

PLEASE NOTE, the SQL is handled dynamically by the SQL Server, therefore some of what you see in my WHERE clause's will look odd to you.

I wrote this report query to pull my data:

SET NOCOUNT ON

SELECT
Procedures.Code ,
cusMMProcFS.ProceduresID ,
FeeSchedule.FeeScheduleId ,
FeeSchedule.FeeScheduleName ,
cusCRIMedlists.MedlistsId ,
cusCRIMedlists.Code

FROM
Procedures
LEFT JOIN cusMMProcFS ON Procedures.ProceduresId = cusMMProcFS.ProceduresID
LEFT JOIN ProceduresFeeSchedule ON cusMMProcFS.ProceduresFeeScheduleId = ProceduresFeeSchedule.ProceduresFeeScheduleId
LEFT JOIN FeeSchedule ON ProceduresFeeSchedule.FeeScheduleId = FeeSchedule.FeeScheduleId
LEFT JOIN cusCRIMedlists ON cusMMProcFS.BSTypeMID = cusCRIMedlists.MedlistsID AND TableName = 'MMBSBSTYPE'

WHERE
--Filter on Fee Schedule
(
(NULL IS NOT NULL AND FeeSchedule.FeeScheduleId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Procedure(s)
(
(NULL IS NOT NULL AND Procedures.ProceduresID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Benefit Set Type
(
(NULL IS NOT NULL AND cusCRIMedlists.MedlistsId IN (NULL)) OR
(NULL IS NULL)
)

With this, I need to update the BSTypeMID from the cusMMProcFS table. When I use the update query below it completes, but my data does not update. I must have missed some obvious coding.

My Update Query:

SET NOCOUNT ON

DECLARE
@ProceduresID int,
@FeeScheduleID int,
@BSTypeMID int,
@CPTCode varchar(10),
@LogonId varchar(30)

SELECT @FeeScheduleID = NULL,
@BSTypeMID = 2346,
@ProceduresID = 868,
@LogonId = dbo.GetLogonId()

DECLARE cTmp CURSOR STATIC FORWARD_ONLY LOCAL FOR

SELECT
cusMMProcFS.ProceduresID ,
FeeSchedule.FeeScheduleId ,
cusCRIMedlists.MedlistsId

FROM
Procedures
LEFT JOIN cusMMProcFS ON Procedures.ProceduresId = cusMMProcFS.ProceduresID
LEFT JOIN ProceduresFeeSchedule ON cusMMProcFS.ProceduresFeeScheduleId = ProceduresFeeSchedule.ProceduresFeeScheduleId
LEFT JOIN FeeSchedule ON ProceduresFeeSchedule.FeeScheduleId = FeeSchedule.FeeScheduleId
LEFT JOIN cusCRIMedlists ON cusMMProcFS.BSTypeMID = cusCRIMedlists.MedlistsID AND TableName = 'MMBSBSTYPE'

WHERE
--Filter on Procedure(s)
(
('868' IS NOT NULL AND Procedures.ProceduresID IN (868)) OR
('868' IS NULL)
)

OPEN cTmp

FETCH NEXT FROM cTmp INTO @ProceduresID, @FeeScheduleID, @BSTypeMID

WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE
cusMMProcFS
SET
BSTypeMID = @BSTypeMID,
LastModified = getdate(),
LastModifiedBy = @LogonId

FETCH NEXT FROM cTmp INTO @ProceduresID, @FeeScheduleID, @BSTypeMID

END
CLOSE cTmp
DEALLOCATE cTmp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-17 : 13:24:26

i dont think posting query will help us much. it would be better if you could explain what you want by posting some data and reqd output from it

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-03-17 : 13:25:01

Hmmmm.

Your UPDATE has no WHERE clause. Or a FROM clause to target specific data.

Do you *REALLY* want to update every row with the same data every time you go through this loop?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

JeffS23
Posting Yak Master

212 Posts

Posted - 2009-03-17 : 13:29:34

Transact Charlie -

I need to somehow join the two tables (cusMMProcFS) and (Procedures) and update the Procedure code. I am stumped. I thought my select would isolate the individual ones. I am lost and sure could use some more help.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-03-18 : 05:34:12

What Viskah said!

An example dataset and desired output would be really helpful.

However -- I don't think you need the cursor at all -- you can do this UPDATE in a SET based way. After taking apart your cursor I get this:


UPDATE cmp SET
	[BSTypeMID] = crm.[MedlistsId]
	, [LastModified] = GETDATE()
	, [LastModifiedBy] = cmp.[proceduresID]
FROM
	procedures p
	LEFT JOIN cusMMProcFS cmp ON cmp.[procedureId] = p.[proceduresId]
	LEFT JOIN proceduresFeeSchedule pfs ON pfs.[ProceduresFeeScheduleId] = cmp.[ProceduresFeeScheduleId]
	LEFT JOIN feeSchedule fs ON fs.[FeeScheduleId] = pfs.[FeeScheduleId]
	LEFT JOIN curCRIMedlists crm ON
		crm.[MedlistsID] = cmp.[BSTypeMID]
		AND crm.[TableName] = 'MMBSBSTYPE'
WHERE	
	--Filter on Procedure(s)
	(
		('868' IS NOT NULL AND Procedures.ProceduresID IN (868))
		OR
		('868' IS NULL)
	)

Is that what you needed? -- Don't run this on production! -- I have no idea about your data structure.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources