Store Procedure in SQL??

Can you take a look at this and see if this is correct?

Say I have two tables like this:

People(pid,name,eid) pid is PK, auto increment
Employee(eid,type) pid is pk, auto increment

Store Procedure for creating new employee:

CREATE PROCEDURE [dbo].[CreateNewEmployee]
@name varchar(255),
@type varchar(10),
@NewID int OUT

AS
declare @tmpid int
INSERT INTO Employee(type)VALUES(@type)
SET @tmpid = SCOPE_IDENTITY()
GO

INSERT INTO People(name,eid)VALUES(@name,@tmpid)
SET @NewID = SCOPE_IDENTITY()
Go


IS this correct??
dkim18Asked:
Who is Participating?
 
dqmqCommented:
Try this:
CREATE PROCEDURE [dbo].[CreateNewEmployee]
@name varchar(255),
@type varchar(10),
@NewID int OUT
AS
BEGIN
declare @tmpid int
declare @trancount int
set @trancount = @@trancount
IF @TRANCOUNT = 0 BEGIN TRANSACTION
INSERT INTO Employee(type) VALUES(@type)
SET @tmpid = SCOPE_IDENTITY()
INSERT INTO People(name,eid) VALUES(@name,@tmpid)
SET @NewID = SCOPE_IDENTITY()
IF @TRANCOUNT = 0 COMMIT
END
Go

Having said that, the data model does not look so good.  To me, it says that one employee can have multiple people.  Well, it's not a show-stopper, but I think this design is better:

People(pid,name) pid is PK, auto increment
Employee(pid,type) pid is pk, pid is also fk referencing People

Then the procedure looks like this:

CREATE PROCEDURE [dbo].[CreateNewEmployee]
@name varchar(255),
@type varchar(10),
@NewID int OUT
AS
BEGIN
declare @trancount int
set @trancount = @@trancount
IF @TRANCOUNT = 0 BEGIN TRANSACTION
INSERT INTO People(name) VALUES(@name)
SET @NewID = scope_identity()
INSERT INTO Employee(pid, type) VALUES(@newID,@type)
IF @TRANCOUNT = 0 COMMIT
END
Go








 

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.