Procedure -insert - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Procedure -insert

Author

Topic

kwacz23
Starting Member

44 Posts

Posted - 2012-11-18 : 14:53:29

Hi,

I have tree tables:

Table Users:

id FirstName id_team id_position
1 Max 1 1
2 Marry 1 2

Table Team

ID Name_Team
1 Payments
2 IT
3 Accountance

Table Position

ID Name_position
1 Specialist
2 Team_leader
3 Manager

I am going to crate insert procudre to add new employee but I don't want to do like this

create procedure Add_employee
@id int,
@id_team int,
@id_postion

as begin
insert users (id,id_team,id_team)
values (@id,id@team,@id_postion)

exec add_employee 4,2,3

insead of numbers I would like to put the name of team and position
like this:
exec add_employee 4,'IT','Team_leader'

Could you help me to recrate this procedure?
Thanks !

I hope this make sense :)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-18 : 17:19:45

What do you want to do if the Team Name or Position Name that is passed in does not exists? The following assumes that that generates an error.

CREATE PROC dbo.add_employee
	@id INT,
	@TeamName VARCHAR(64),
	@PositionName VARCHAR(64)
AS

DECLARE @TeamId INT;
SELECT @TeamId = ID FROM Team WHERE Name_Team = @TeamName;
IF (@TeamId IS NULL)
BEGIN
	RAISERROR('Invalid team name',16,1)
	RETURN;
END

DECLARE @positionId INT;
SELECT @PositionId = ID FROM Position WHERE Name_Position = @PositionName;
IF (@PositionId IS NULL)
BEGIN
	RAISERROR('Invalid position name',16,1)
	RETURN;
END

-- TODO: Check if the passed in UserID already exists and raise error if it does.

INSERT INTO users 
(id, id_team, id_position)
VALUES
(@id, @teamID, @PositionId);
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources