CODEWITHSUNDEEP
sqldynamic-sql
Abdullah
Abdullah

Reputation: 307

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'

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"

Upvotes: 1

Views: 261

Answers (4)

Abdullah
Abdullah

Reputation: 307

Thanks balexandre. The final query after minor modification(casting @From,@To into varchar) is:

CREATE PROCEDURE sp_GetMyStuff

@TableName VARCHAR(128),
@From DATETIME,
@To DATETIME

AS

DECLARE @sql VARCHAR(4000)
SELECT @sql = '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,'''+ CAST(@From AS VARCHAR(100)) +''' ), 0)
AND DATEADD(dd, DATEDIFF(dd, 0,'''+ CAST(@To AS VARCHAR(100)) +'''), 0))
ORDER BY ''Inspect Date Time'''
EXEC (@sql)

GO

Upvotes: 0

balexandre
balexandre

Reputation: 75073

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
');

and then just call it

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

Upvotes: 3

davek
davek

Reputation: 22895

OK, assuming you're using SQL Server (judging by the DATEADD and DATEDIFF functions), you'll need to

  1. construct a concatenated sql command as string (taking care not to allow SQL injection: i.e. you should check that your table_name variable is a valid table name by looking up possible names form information_schema and validating etc.)

  2. execute your dynamic sql using sp_executesql: http://msdn.microsoft.com/en-us/library/ms188001.aspx

Upvotes: 3

marc_s
marc_s

Reputation: 754268

In SQL Server, if you want to "parametrize" the table name, you have to use dynamic SQL

If so, you must read Erland's The Curse and Blessing of dynamic SQL as an intro.

So basically, you need to build up your SQL statement as a string, and then execute it. There is no other way to "parametrize" the table name in a SQL Server T-SQL statement.

Upvotes: 3

Related Questions