Geeks With Blogs

AzamSharp Some day I will know everything. I hope that day never comes.
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


Posted on Sunday, June 26, 2005 10:51 PM | Back to top


Comments on this post: Inserting Articles by same author in the database ( SQL Stored Procedure )

# re: Inserting Articles by same author in the database ( SQL Stored Procedure )
Requesting Gravatar...
Couple of things.
(1) You could do it in a simple way as :

IF NOT EXISTS(SELECT * FROM Authors WHERE Email = @Email )
BEGIN
-- Do the insert
INSERT INTO....
-- Get the ID using SCOPE_IDENTITY()
END

INSERT INTO Articles...

Here you dont need to have the INSERT INTO Articles twice. Just bring it out of the loop.
(2) You should ALWAYS use SCOPE_IDENTITY() and NOT @@IDENTITY. Check out Books On Line on how they are different.
Left by Dinakar. on Jun 30, 2005 4:46 PM

# re: Inserting Articles by same author in the database ( SQL Stored Procedure )
Requesting Gravatar...
Thanks,

:)
Left by Azamsharp on Jun 30, 2005 5:31 PM

Your comment:
 (will show your gravatar)


Copyright © Mohammad Azam | Powered by: GeeksWithBlogs.net