CODEWITHSUNDEEP
sqlsql-serverstored-procedures
StudentRik
StudentRik

Reputation: 1049

Join on select SQL Server stored procedure

I am trying to create a stored procedure to insert data into 2 tables in SQL Server.

I have tried putting the join in all different positions of the code and still get an error.

 CREATE PROCEDURE sp_Insert_Person
 @s_FirstName nvarchar(50),
 @s_Surname nvarchar(50),
 @s_AddressLine1 nvarchar(50),
 @s_AddressLine2 nvarchar(50),
 @s_Postcode nvarchar(10),
 @s_Phone nvarchar(50),
 @s_Department nvarchar(50)
 AS
BEGIN
INSERT INTO 
        tbl_person(FirstName, Surname, AddressLine1, AddressLine2, 
                   Postcode, Phone, tbl_Department.Department)
INNER JOIN tbl_person
ON  tbl_person.DepartmentID = tbl_Department.DepartmentID

VALUES (@s_FirstName,
        @s_Surname,
        @s_AddressLine1,
        @s_AddressLine2,
        @s_Postcode,
        @s_Phone,
        @s_Department)

END

I have tried the join at the end and at the beginning I have looked all over for insert joins, wondered if i was just getting it all wrong.

I have a department table and a person table and thought I would be able to access the department table through the FK DepartmentID which I have in the Person table, as is the PK in the Department table

Upvotes: 0

Views: 3239

Answers (2)

bummi
bummi

Reputation: 27367

 CREATE PROCEDURE sp_Insert_Person
 @s_FirstName nvarchar(50),
 @s_Surname nvarchar(50),
 @s_AddressLine1 nvarchar(50),
 @s_AddressLine2 nvarchar(50),
 @s_Postcode nvarchar(10),
 @s_Phone nvarchar(50),
 @s_Department nvarchar(50)
 AS
BEGIN
if not Exists(select * from tbl_Department where Department=@s_Department)
   insert into tbl_Department (Department) Values (@s_Department)

INSERT INTO tbl_person
                     (FirstName,
                     Surname, 
                     AddressLine1, 
                     AddressLine2, 
                     Postcode, 
                     Phone, 
                     DepartmentID)

select @s_FirstName,
        @s_Surname,
        @s_AddressLine1,
        @s_AddressLine2,
        @s_Postcode,
        @s_Phone,
        @s_Department,
        DepartmentID 
        from tbl_Department 
        where Department=@s_Department
END

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

I think something like this

INSERT INTO tbl_person
                     (FirstName,
                     Surname, 
                     AddressLine1, 
                     AddressLine2, 
                     Postcode, 
                     Phone, 
                     DepartmentID)
Select @s_FirstName,
        @s_Surname,
        @s_AddressLine1,
        @s_AddressLine2,
        @s_Postcode,
        @s_Phone, 
        tbl_Department.DepartmentID 
from  tbl_person 
join DepartmentID
ON  tbl_person.DepartmentID = tbl_Department.DepartmentID 
where tbl_Department.Department = @s_Department

Upvotes: 1

Related Questions