How to set table name in dynamic SQL query

Question

I want to set table name in a dynamic SQL query  I tried successfully for parameter as following      Using sp executesql       Build and Execute a Transact-SQL String with a single parameter  value Using sp executesql Command        Variable Declaration    DECLARE  EmpID AS SMALLINT DECLARE  SQLQuery AS NVARCHAR 500  DECLARE  ParameterDefinition AS NVARCHAR 100     set the parameter value    SET  EmpID   1001    Build Transact-SQL String by including the parameter    SET  SQLQuery    SELECT   FROM tblEmployees WHERE EmployeeID    EmpID      Specify Parameter Format    SET  ParameterDefinition      EmpID SMALLINT     Execute Transact-SQL String    EXECUTE sp executesql  SQLQuery   ParameterDefinition   EmpID   Now I want to take TABLE NAME dynamically using a parameter but I ve failed to do that  Please guide me

User · Answer

This is the best way to get a schema dynamically and add it to the different tables within a database in order to get other information dynamically

select @sql = 'insert #tables SELECT ''[''+SCHEMA_NAME(schema_id)+''.''+name+'']'' AS SchemaTable FROM sys.tables'

exec (@sql)

of course #tables is a dynamic table in the stored procedure

User · Answer

Building on a previous answer by  user1172173 that addressed SQL Injection vulnerabilities  see below   CREATE PROCEDURE  dbo   spQ SomeColumnByCustomerId    CustomerId int   SchemaName varchar 20    TableName nvarchar 200   AS SET Nocount ON DECLARE  SQLQuery AS NVARCHAR 500  DECLARE  ParameterDefinition AS NVARCHAR 100  DECLARE  Table ObjectId int  DECLARE  Schema ObjectId int  DECLARE  Schema Table SecuredFromSqlInjection NVARCHAR 125   SET  Table ObjectId   OBJECT ID  TableName  SET  Schema ObjectId   SCHEMA ID  SchemaName  SET  Schema Table SecuredFromSqlInjection   SCHEMA NAME  Schema ObjectId          OBJECT NAME  Table ObjectId   SET  SQLQuery   N SELECT TOP 1      Schema Table SecuredFromSqlInjection     SomeColumn  FROM dbo Customer  INNER JOIN      Schema Table SecuredFromSqlInjection      ON dbo Customer Customerid        Schema Table SecuredFromSqlInjection     CustomerId  WHERE dbo Customer CustomerID    CustomerIdParam  ORDER BY      Schema Table SecuredFromSqlInjection     SomeColumn DESC   SET  ParameterDefinition    N  CustomerIdParam INT   EXECUTE sp executesql  SQLQuery   ParameterDefinition   CustomerIdParam    CustomerId  RETURN

User · Answer

To help guard against SQL injection  I normally try to use functions wherever possible   In this case  you could do       SET  TableName     lt  db   gt  lt  schema   gt tblEmployees  SET  TableID     OBJECT ID TableName  --won t resolve if malformed injected      SET  SQLQuery    SELECT   FROM     OBJECT NAME  TableID      WHERE EmployeeID    EmpID

User · Answer

Table names cannot be supplied as parameters  so you ll have to construct the SQL string manually like this   SET  SQLQuery    SELECT   FROM      TableName     WHERE EmployeeID    EmpID     However  make sure that your application does not allow a user to directly enter the value of  TableName  as this would make your query susceptible to SQL injection  For one possible solution to this  see this answer

User · Answer

Try this       Variable Declaration    DECLARE  EmpID AS SMALLINT DECLARE  SQLQuery AS NVARCHAR 500  DECLARE  ParameterDefinition AS NVARCHAR 100  DECLARE  TableName AS NVARCHAR 100     set the parameter value    SET  EmpID   1001 SET  TableName    tblEmployees     Build Transact-SQL String by including the parameter    SET  SQLQuery    SELECT   FROM      TableName     WHERE EmployeeID    EmpID      Specify Parameter Format    SET  ParameterDefinition      EmpID SMALLINT     Execute Transact-SQL String    EXECUTE sp executesql  SQLQuery   ParameterDefinition   EmpID

[sql] How to set table name in dynamic SQL query?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to parameters

Examples related to dynamicquery