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 db

ils
SET OilName = @myString
WHERE OilID = myID
UPDATE dbo.Cautions
SET Description = @myString
WHERE CautionID = myID
DELETE db

ils
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