Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Store Procedure in SQL??

Posted on 2007-07-23
1
Medium Priority
?
251 Views
Last Modified: 2013-12-07
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??
0
Comment
Question by:dkim18
1 Comment
 
LVL 42

Accepted Solution

by:
dqmq earned 2000 total points
ID: 19551507
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question