title: SQL Server : passing table name as parameter to insert into a variable table
body: <p>I have a stored procedure that I use a while loop to select from a table and insert the results into a variable table on several rows according to their index number. I want to be able to pass the table name as a parameter, but I can't figure out how. I'm putting here the store procedure to see if you guys can help me. Thanks!</p>
  
  <pre><code>USE [Enrollment] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
  
  ALTER PROCEDURE [dbo].[Enrollment_By_Objective] --exec Enrollment_By_Objective @TableName = 'Students'
  
  @TableName VARCHAR (50)
  
  AS
  
  BEGIN
  
  SET NOCOUNT ON
  
  DECLARE @EO_CODE INT 
         ,@EO_NAME VARCHAR(100) 
         ,@DHC INT
         ,@PCA INT
         ,@PC INT
         ,@SE INT
         ,@COA INT
         ,@CO INT
         ,@NCL INT
  
  DECLARE @EnrollmentData TABLE 
  (
       EnrollmentObjective NVARCHAR(100)
      ,DistinctHeadCount INT
      ,ProgramCompletersAll INT
      ,ProgramCompleters INT
      ,StillEnrolled INT
      ,CompletedObjectiveAll INT
      ,CompletedObjective INT
      ,NonCompletedLeavers INT
  )
  
  SET @EO_CODE = 1
  WHILE (@EO_CODE &lt;= 10)
      BEGIN
          SET @EO_NAME = @EO_CODE
          SET @DHC = (SELECT COUNT (*) FROM(SELECT DISTINCT StudentID FROM **@TableName** WHERE IndexEnrollmentObjective = @EO_CODE) X)
          SET @PCA = (SELECT COUNT (*) FROM(SELECT DISTINCT StudentID FROM Students WHERE ExitCode = 'P' AND IndexEnrollmentObjective = @EO_CODE) X)
          SET @PC = (SELECT COUNT (*) FROM(SELECT DISTINCT StudentID FROM Students WHERE ExitCode = 'P' AND Outcome IN ('R', 'E', 'I') AND IndexEnrollmentObjective = @EO_CODE) X)
          SET @SE = (SELECT COUNT (*) FROM(SELECT DISTINCT StudentID FROM Students WHERE ExitCode IS NULL  AND IndexEnrollmentObjective = @EO_CODE) X)
          SET @COA = (SELECT COUNT (*) FROM(SELECT DISTINCT StudentID FROM Students WHERE ExitCode IN ('C', 'N', 'S') AND IndexEnrollmentObjective = @EO_CODE) X)
          SET @CO = (SELECT COUNT (*) FROM(SELECT DISTINCT StudentID FROM Students WHERE ExitCode IN ('C', 'N', 'S') AND Outcome IN ('R', 'E', 'I')  AND IndexEnrollmentObjective = @EO_CODE) X)
          SET @NCL = (SELECT COUNT (*) FROM(SELECT DISTINCT StudentID FROM Students WHERE ExitCode NOT IN ('C', 'N', 'P','S')  AND IndexEnrollmentObjective = @EO_CODE) X)
  
          INSERT INTO @EnrollmentData
              SELECT @EO_NAME, @DHC, @PCA, @PC, @SE, @COA, @CO, @NCL
              WHERE @DHC &gt; 0
  
          SET @EO_CODE = @EO_CODE + 1
      END
  
  UPDATE @EnrollmentData
  SET EnrollmentObjective = 
  (
      SELECT
          CASE WHEN EnrollmentObjective = '1' THEN 'Certificate Seekers'
               WHEN EnrollmentObjective = '2' THEN 'Occupational Upgrade'
               WHEN EnrollmentObjective = '3' THEN 'Senior Citizen'
               WHEN EnrollmentObjective = '4' THEN 'Bad Data'
               WHEN EnrollmentObjective = '5' THEN 'Apprenticeship'
               WHEN EnrollmentObjective = '6' THEN 'Basic Skills'
               WHEN EnrollmentObjective = '7' THEN 'Life Long Learning'
               WHEN EnrollmentObjective = '8' THEN 'Assessment'
               WHEN EnrollmentObjective = '9' THEN 'Secondary'
               WHEN EnrollmentObjective = '10' THEN 'Job Re-entry'
               ELSE EnrollmentObjective 
          END
  )
  
  SELECT * FROM @EnrollmentData
  END
  </code></pre>