Stored Procedure for multiple inserts

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedure for multiple inserts

Author

Topic

jodar
Starting Member

1 Post

Posted - 2008-02-24 : 03:44:57

Hello all,

I'm working on a project for fun with some friends and have run into an issue with stored procedures. I've dealt with SQL quite a bit at my current job, but always from the perspective of somebody querying the database. The database was always managed by someone else and I never had to worry about the underlying code. Now, with my own project at home, I'm trying to deal with a situation and would like to use one, but I'm not sure if it is the best option and if so, exactly how to go about it.

Imagine a site that tracks movies. I have 3 tables:

Movies ( MovieID, Title, DirectorID, ActorID )
Actors ( ActorID, Name )
Director (DirectorID, Name)

This is an overly simple example, but it gets to the heart of my problem.

Okay, now what I'm wanting to do is to be able to write a procedure that would let me create my entries from just one call -- for instance

create_movie( 'Super Movie', 'directorJoe', 'actorJohn' )

that would do the following things:
-Look and see if the given director and actor already exist (from previous films)
-If they do, grab their ID values and use those in the new movie entry
-If they do not, create new entries and get THOSE ID values to use in the new movie entry

Can this be done in a stored procedure (I'm pretty sure it can be) and what sort of commands should I look into -- I'm not looking for a complete solution, cause I want to learn, but I am having trouble finding examples that fit my scenario.

Thanks.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-24 : 04:47:59

Based on what you have supplied , the below will give you some ideas.
CREATE PROCEDURE MovieEntry
@movie VARCHAR(100),
@director VARCHAR(100),
@actor VARCHAR(100)

AS
DECLARE @directorID INT ,@actorID INT ,@movieID INT
SET @directorID = (SELECT directorID FROM Directors WHERE directorName = @director)
IF @directorID IS NULL
BEGIN

INSERT INTO Directors (directorName)VALUES (@director)
SET @directorID = SCOPE_IDENTITY()
END

SET @actorID = (SELECT actorID FROM Actors WHERE actorName = @actor)
IF @directorID IS NULL
BEGIN

INSERT INTO Actots (ActorName)VALUES (@actor)
SET @actorID = SCOPE_IDENTITY()
END

BEGIN TRANSACTION
INSERT INTO Movies (movieTitle)VALUES (@movie)
SET @movieID = SCOPE_IDENTITY()
INSERT INTO MoviesDirectors(movieID , DirectoID)VALUES(@movieId,@directorID)
INSERT INTO MovieActors(movieID,actorID)VALUES(@movieId,@actorID)
COMMIT TRANSACTION

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-24 : 08:30:05

quote:
Originally posted by jackv

Based on what you have supplied , the below will give you some ideas.
CREATE PROCEDURE MovieEntry
@movie VARCHAR(100),
@director VARCHAR(100),
@actor VARCHAR(100)

AS
DECLARE @directorID INT ,@actorID INT ,@movieID INT
SET @directorID = (SELECT directorID FROM Directors WHERE directorName = @director)
IF @directorID IS NULL
BEGIN

INSERT INTO Directors (directorName)VALUES (@director)
SET @directorID = SCOPE_IDENTITY()
END

SET @actorID = (SELECT actorID FROM Actors WHERE actorName = @actor)
IF @actorID IS NULL
BEGIN

INSERT INTO Actor (ActorName)VALUES (@actor)
SET @actorID = SCOPE_IDENTITY()
END

BEGIN TRANSACTION
INSERT INTO Movies (movieTitle)VALUES (@movie)
SET @movieID = SCOPE_IDENTITY()
INSERT INTO MoviesDirectors(movieID , DirectoID)VALUES(@movieId,@directorID)
INSERT INTO MovieActors(movieID,actorID)VALUES(@movieId,@actorID)
COMMIT TRANSACTION

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Small typos

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-25 : 01:42:41

Thanks , should have used the Parser

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources