SQL Server :将表名作为参数传递以插入到变量表中

Question

我有一个存储过程，我使用while循环从表中进行选择，并根据索引号将结果插入到多行的变量表中。我希望能够将表名作为参数传递，但我不知道如何传递。我把存储过程放在这里，看看你们能不能帮我。谢谢!

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 <= 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 > 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

Answer 1

使用dynamic SQL生成语句，然后使用带有输出参数的sp_executeSQL检索结果。

仅供参考，请不要子查询简单的COUNT DISTINCT，只需如下所示：

declare @sql nvarchar(max)
set @sql = N'select @DHC = count(distinct studentid) from ' + @TableName + ' where IndexEnrollmentObjective = @EO_CODE'
exec sp_executesql @sql, N'@DHC int output,@EO_CODE INT', @DHC output, @EO_CODE

这3行放在你有行的地方

SET @DHC = (SELECT COUNT (*) FROM(SELECT DISTINCT StudentID FROM **@TableName** WHERE IndexEnrollmentObjective = @EO_CODE) X)