I am working on a website where user can submit an article. I have two tables "Articles" and "Authors". Articles contains the information about article like Title, Description etc and Authors contains the name and email of the author.
Each new article entry form has a field to enter the name and email of the author. If the user authors several article we don't want to make a new entry in the author's table since that author already exists. This stored procedure checks if the author already exists in the authors table is given below. If the author is already present in the database than we just add his new article into the article table otherwise a new author and article is created.
CREATE PROCEDURE usp_InsetArticle
@Title nvarchar(50),
@Abstract text,
@Description text,
@CategoryID int,
@Name nvarchar(50),
@Email nvarchar(50)
AS
DECLARE @AuthorID int
-- selects the AuthorID from the Author table
SELECT @AuthorID = AuthorID FROM Authors WHERE Email = @Email
-- This means that the author is already present in the database
IF @AuthorID != 0
BEGIN
-- Inserts into the Articles
INSERT INTO Articles(Title,Abstract,[Description],CategoryID,
AuthorID,DateCreated,DateModified)
VALUES(@Title,@Abstract,@Description,@CategoryID,@AuthorID,GETDATE(),GETDATE())
END
-- This means that its a new author and not present in the database
ELSE
BEGIN
-- Inserts into Authors
INSERT INTO Authors([Name],Email)
VALUES(@Name,@Email)
SELECT @AuthorID = @@IDENTITY
INSERT INTO Articles(Title,Abstract,[Description],CategoryID,
AuthorID,DateCreated,DateModified)
VALUES(@Title,@Abstract,@Description,@CategoryID,@AuthorID,GETDATE(),GETDATE())
END
GO