Cursor Writing

[blackbirdMIT]

I need help in developing an updatable cursor that updates and deletes rows from a table/s. I only like to deal with Oil.OilName and Cautions.Description.

I have the tables below:

OIL table:
OilID int
OilName nvarchar(50)
LatinName nvarchar(50)
PlantTypeID int
PlantPartID int

CAUTIONS table:
CautionID int
CautionNum int
Caution nvarchar(50)
Description ntext(16)

Can you please show me how to start this task?

 

[LogicalmanUS]

sr727,

I think I need a little more information as to what you intend to do with the tables, e.g. what records need updating, deleting etc, and, if this involves both the above tables, then where is the key field link?

One thing you should also consider is that Cursers are not always the best way to go (ask any DBA with less than 16 processors) and maybe you should also consider Hash and derived tables with subqueries.

Logicalman

 

[blackbirdMIT]

Below is my query: (This lets me view the Oil Name and Caution. I would like to be able to update or delete oilname and/or caution items.)

SELECT dbils.OilName AS [Oil Name], dbo.Cautions.Description AS Caution
FROM dbo.Cautions INNER JOIN
dbilCautions ON dbo.Cautions.CautionID = dbilCautions.CautionID INNER JOIN
dbils ON dbilCautions.OilID = dbils.OilID

Sample result:
Oil Name Caution
Lemon Grass Do not consume alcohol while using this oil.
Basil Overuse could damage kidneys.

 

[LogicalmanUS]

sr727,

OK. I re-created the tables (including the one missing from the first posting - OilCautions), and all appears quite normal.

To answer your question as to how to change or delete the Oil.OilName and Cautions.Description columns, this would depend on under what circumstances are the changes/deletions made.

Below are the standard TSQL statements for Updating and Deleting rows. In all cases the Key ID fields are required. They are represented by the term myID below, and would be passed to the Sproc, together with, for Updates only, @myString as the new String:

UPDATE dbils
SET OilName = @myString
WHERE OilID = myID

UPDATE dbo.Cautions
SET Description = @myString
WHERE CautionID = myID

DELETE dbils
WHERE OilID = myID

DELETE dbo.Cautions
WHERE CautionID = myID


To create a SProc to do this, use the following Syntax (copy and paste as new SProc), usage examples are included:

<CODE>
-- Created by Logicalman for Tek Tips 09/24/03
-- thread183-662403

-- This Sproc will cause an Update or Delete on the Specified Table/Column dependant on the passed ID
-- Use Syntax: EXEC usp_UPDATE_DELETE @iID @sTEXT @sTABLE @iTYPE
-- Example: EXEC usp_UPDATE_DELETE 3, 'New text', 'OIL', 1
-- Causes Update to OIL table, replacing OilName with 'New Text' Where OilID = 3
-- Example: EXEC usp_UPDATE_DELETE 5, '', 'OIL', 2
-- Causes DELETE to OIL table Where OilID = 5

CREATE PROCEDURE [dbo].[usp_UPDATE_DELETE]
@iID int, -- ID Number to Update
@sTEXT varchar (100), -- Text used for Updates only
@sTABLE varchar(100), -- Which Table to update/delete from
@iTYPE int -- 1=Update 2=Delete
AS

DECLARE @sSQL varchar(1000) -- hold TSQL statement to execute
DECLARE @sCOL varchar(100) -- Column to Update
DECLARE @iIDCOL varchar(100) -- Identity Column

IF @sTABLE = 'OIL'
SET @sCOL = 'OilID'
ELSE
SET @sCOL = 'CautionID '

SET @sSQL = ''
IF @iTYPE=1 -- UPDATE
BEGIN
IF @sTABLE = 'OIL'
SET @sCOL = 'OilName'
ELSE
SET @sCOL = 'Description '

SET @sSQL = @sSQL + ' UPDATE ' + @sTABLE
SET @sSQL = @sSQL + ' SET ' + @sCOL + ' = ' + CHAR(39) + @sTEXT + CHAR(39)
SET @sSQL = @sSQL + ' WHERE '
SET @sSQL = @sSQL + @sCOL + '=' + CAST(@iID AS varchar(10))
EXEC (@sSQL)
PRINT @sSQL

END
ELSE -- DELETE
BEGIN
SET @sSQL = @sSQL + ' DELETE ' + @sTABLE
SET @sSQL = @sSQL + ' WHERE '
SET @sSQL = @sSQL + @sCOL + '=' + CAST(@iID AS varchar(10))
EXEC (@sSQL)
PRINT @sSQL
-- Remember to delete the reference to this ID in the OilCaution table
-- Unless there is a Constraint with Update delete between the tables
SET @sSQL = ''
SET @sSQL = @sSQL + ' DELETE OilCautions '
SET @sSQL = @sSQL + ' WHERE '
SET @sSQL = @sSQL + @sCOL + '=' + CAST(@iID AS varchar(10))
EXEC (@sSQL)
PRINT @sSQL
END
GO
</CODE>

Logicalman