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

Mahesh19...

SOLVED CLOSED ACTIVE ANSWERED
User: Mahesh1986
Posted: on Jan 17, 2017 06:05 AM
Forum: SQL Server
Answer: 1
Views: 9843

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.

Download FREE API for Word, Excel and PDF in ASP.Net

kalpesh

0 10

Reply
ANSWER
User: kalpesh
Replied: on Jan 25, 2017 04:52 AM
Modified: on Jan 25, 2017 04:58 AM
Report

Try this sample query in which i created Temp table.

CREATE PROCEDURE DeleteJodDetailsByUserId
    @UserId INT
AS
BEGIN
    SET NOCOUNT ON;
 
    -- CREATE Temp Table Which will store the JobId In @JobTable temp table
    DECLARE @JobTable AS TABLE (JobId INT)

	-- Get The JobId by UserId From Company Table to Temp JobTable
	INSERT INTO @JobTable
	SELECT JobID FROM Jobs
	WHERE CompanyID IN (SELECT CompanyID
						FROM Company
						WHERE UserID = @UserId)

    -- FIRST DELETE Child table data from JobRequiredSkills
    DELETE FROM JobRequiredSkills
    WHERE JobID IN (SELECT JobId FROM @JobTable)   
   

    -- Secondly DELETE Child table data from JobRequiredLanguages
   DELETE FROM JobRequiredLanguages
   WHERE JobID IN (SELECT JobId FROM @JobTable)

   -- Then you can delete DELETE Parent Table  data from Job
   DELETE FROM Jobs
   WHERE JobID IN (SELECT JobId FROM @JobTable)
END
GO

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

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

Company

Explore

Follow us

Contact Us