Getting table Foreign Key from stored procedure

Question

I have Employee table where I have BranchOfficeId foreignKey. How can I modify stored procedure to get all BranchOfficeId of Employee Table?.

Stored procedure:

USE [AM_DB]
GO
/****** Object:  StoredProcedure [dbo].[GetEmployeeTraining]    Script Date: 17/10/2016 10:47:34 a. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetEmployeeTraining]
    -- Add the parameters for the stored procedure here
     @IdTraining INT = null
    ,@IdArea int = null
    ,@IdDepartment int = null
    ,@Type VARCHAR(15) 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @query varchar(100)

    Create Table #Type(
    value int
    )

    SET @query = 'INSERT INTO #Type(value) VALUES ' + @Type
    EXEC (@query)

    -- Insert statements for procedure here
    SELECT 
          0 AS Id
         ,0 AS IdTraining
         ,e.ID AS IdEmployee
         ,e.Code AS Code
         ,e.Name +' ' +e.LastName + ' '+ e.SurName AS Name
         ,e.CURP
         ,e.RFC 
         ,e.AdmissionDate
         ,a.Description AS Area
         ,d.Description AS Department
         ,0.0 AS Hours
         ,0.0 AS Score
         ,e.Empresa
        FROM [dbo].[Employee] e (NOLOCK)
        LEFT JOIN [dbo].[Area] a (NOLOCK)
            ON e.Area = a.Id
        LEFT JOIN [dbo].[Department] d (NOLOCK)
            ON d.Id = e.Department
        WHERE 
            ((e.Id NOT IN (SELECT IdEmployee FROM [dbo].[TrainingEmployee] (NOLOCK) WHERE IdTraining = @IdTraining)) OR @IdTraining IS NULL)
            AND 
            ((e.Area = @IdArea) OR @IdArea IS NULL)
            AND
            ((e.Department = @IdDepartment) OR @IdDepartment IS NULL)
            AND
            (e.Type IN (SELECT value FROM #Type))


DROP TABLE #Type;
END

I´m support. If anyone can explain me how stored procedure works and what I need to do to add these field. I really appreciate it

score 0 · Answer 1 · edited May 23 '17 at 10:32

If the BranchOfficeId is in the Employee table, the only thing you will need to do is to add it in the list of columns (shown below). Here is an answer to a question on stored procedures... What is a stored procedure?

SELECT 0 AS Id ,0 AS IdTraining ,e.BrachOfficeId --Add this line ,e.ID AS IdEmployee ,e.Code AS Code ,e.Name +' ' +e.LastName + ' '+ e.SurName AS Name ,e.CURP ,e.RFC ,e.AdmissionDate ,a.Description AS Area ,d.Description AS Department ,0.0 AS Hours ,0.0 AS Score ,e.Empresa FROM [dbo].[Employee] e (NOLOCK) LEFT JOIN [dbo].[Area] a (NOLOCK) ON e.Area = a.Id LEFT JOIN [dbo].[Department] d (NOLOCK) ON d.Id = e.Department WHERE ((e.Id NOT IN (SELECT IdEmployee FROM [dbo].[TrainingEmployee] (NOLOCK) WHERE IdTraining = @IdTraining)) OR @IdTraining IS NULL) AND ((e.Area = @IdArea) OR @IdArea IS NULL) AND ((e.Department = @IdDepartment) OR @IdDepartment IS NULL) AND (e.Type IN (SELECT value FROM #Type))

Getting table Foreign Key from stored procedure

1 Answers1