MySQL confirguration script

DELIMITER //

CREATE TABLE Feeds (
ID int (auto incrementing),
FeedName VARCHAR(256) NOT NULL,
FeedURI VARCHAR(256) NOT NULL,
IsActive BIT NOT NULL,
PRIMARY KEY (ID)
)//


CREATE PROCEDURE UpdateFeed (
IN prmID INT NULL,
IN prmFeedName VARCHAR(256) NULL,
IN prmFeedURI VARCHAR(256) NULL,
IN prmIsActive BIT NULL,
OUT prmErrorID int
)

-- This sproc manages entries in the Feeds table
-- a valid non-zero value for prmID will trigger an update
-- this sproc is intended to enable granular changes to Feed records

BEGIN

SET prmErrorID = 0

-- check to see if the specified id points to a valid record in the Feeds table
-- if not then assume it's a mistake and set it to zero

IF prmID != 0 AND NOT EXISTS ( SELECT ID FROM FEEDS WHERE ID = prmID ) THEN
SET prmID = 0
END IF

-- if the id is zero then create a new record,
-- otherwise we update the existing record

IF prmID=0 THEN

-- all fields are optional, but that case only applies when updating
-- existing records, prmFieldName and prmFeedURI are required for new entries

IF IS NOT NULL (prmFeedName) and IS NOT NULL (prmFeedURI) THEN

INSERT INTO Feeds(FeedName, FeedURI, IsActive)
VALUES (prmFeedName, prmFeedURI, 1)

ELSE

-- 4001 can be the error code for an invalid null
-- there must be an error api, but this will do for now
SET prmErrorID = 4001

END IF

ELSE

-- use the current value when the parameter has a null value

UPDATE Feeds
SET
FeedName = CASE
WHEN IS NOT NULL(prmFeedName) THEN prmFeedName ELSE FeedName
END,
FeedURI = CASE
WHEN IS NOT NULL(prmFeedURI) THEN prmFeedURI ELSE FeedURI
END,
IsActive = CASE
WHEN IS NOT NULL(prmIsActive) THEN prmIsActive ELSE IsActive
END
WHERE ID = prmID

END IF

END
//


DELIMITER ;