How to use Temporary table in stored procedure to reuse value for select or delete records of other tables using SQL Server

Mahesh1986 says:

Hi ,

    I need one stored procedure like below.

 

Users Table:

UserID      Name

1               Mahesh

 

Company Table :

CompanyID       Name

1                       Mahesh&Co

 

Jobs:

JobID           CompanyID     JobTitle

1                    1                  Asp.net

2                    1                  Java

3                    1                  Oracle

 

Skills:

SkillID            Name

1                   Ado.net

2                   CoreJava

3                   MySQL

 

 

JobRequiredSkills:

JobRequiredSkillID      JobID     SkillID

1                                1               1

2                                 1              2

3                                2               2

4                                3                3

 

Scenario:

1. Only input we are going to pass is : UserID from users table

2. based on userid input we have to get companyID

3. we have to get all jobs posted with that companyid

4. after getting all jobs we have to delete all data from jobs table as well child table i.e jobrequiredskills.

 

Thanks in advance.

 

There is no relation between User Table and Company Table. How you can match which user belong to which company.

Yeah.Forgot to mention there

Company Table :

CompanyID       Name                UserID

1                       Mahesh&Co           1

Can anyone help regarding this

Refer The Below Query for your reference.

CREATE PROCEDURE DeleteJodDetailsByUserId
	@UserId INT
AS
BEGIN
	SET NOCOUNT ON;

	-- FIRST DELETE Child table data from JobRequiredSkills
	DELETE FROM JobRequiredSkills
	WHERE JobID IN (SELECT JobID FROM Jobs
	               WHERE CompanyID IN (SELECT CompanyID
											FROM Company WHERE UserID = @UserId))

   -- Then you can delete DELETE Parent Table  data from Job
   DELETE FROM Jobs
   WHERE CompanyID IN (SELECT CompanyID
						FROM Company WHERE UserID = @UserId)
END
GO

Hi Kalpesh ,

 Thanks for your reply .and working fine.

But Question is :

If there is another table called JobRequiredLanguages then below line of code which is in bold will repeat again.

DELETE FROM JobRequiredLanguages WHERE JobID IN (SELECT JobID FROM Jobs WHERE CompanyID IN (SELECT CompanyID FROM Company WHERE UserID = @UserId))

Can we achieve this in a better way.