在SQL查询中将我的表名作为参数传递 [英] pass my table name as parameter in sql query

问题描述

我需要查询方面的帮助.在查询中，我想将表名称作为参数传递.这是我的查询:

I need help with a query. In my query I want to pass my table name as parameter. This is my query:

SELECT DISTINCT 
    CONVERT (varchar, InspectDateTime) AS 'Inspect Date Time', 
    CONVERT (varchar, SynDateTime) AS 'Sync Date Time', 
    Employee, 
    ROUND(OverAllPercentage, 2) AS Grade 
FROM 
    Table_Name 
WHERE 
    (DATEADD(dd, DATEDIFF(dd, 0, InspectDateTime), 0) 
    BETWEEN 
        DATEADD(dd, DATEDIFF(dd, 0, @From ), 0) AND 
        DATEADD(dd, DATEDIFF(dd, 0, @To ), 0)) 
ORDER BY 
    'Inspect Date Time'

在这里，我要传递 Table_Name 作为参数.请注意，此查询已使用两个参数作为参数，即"@ From "和" @To "

Here I want to pass the Table_Name as parameter. Please note that this query is already taking two arguments as parameter, namely "@From" and "@To"

推荐答案

如果您使用的是MS SQL，则可以执行以下操作:

If you are working with MS SQL you can do:

CREATE PROCEDURE sp_GetMyStuff
(
    @From datetime,
    @To datetime,
    @TableName nvarchar(100)
)
AS

exec('    
    SELECT DISTINCT 
        CONVERT (varchar, InspectDateTime) AS ''Inspect Date Time'', 
        CONVERT (varchar, SynDateTime) AS ''Sync Date Time'', 
        Employee, 
        ROUND(OverAllPercentage, 2) AS Grade 
    FROM 
        ' + @TableName + ' 
    WHERE 
        (DATEADD(dd, DATEDIFF(dd, 0, InspectDateTime), 0) 
        BETWEEN 
            DATEADD(dd, DATEDIFF(dd, 0, ' + @From + '), 0) AND 
            DATEADD(dd, DATEDIFF(dd, 0, ' + @To + '), 0)) 
    ORDER BY 
        1
');

然后调用它

sp_GetMyStuff '2011-05-05', '2011-06-05', 'TBL_MYTABLE'

这篇关于在SQL查询中将我的表名作为参数传递的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！