Menu
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.
427
First, declare two variables, @table for holding the name of the table from which you want to query and @sql for holding the dynamic SQL. Second, set the value of the @table variable to production. products . Fourth, call the sp_executesql stored procedure by passing the @sql parameter.
Basically dynamic SQL allows you to construct a SQL Statement in the form of a string and then execute it. This is the ONLY way you will be able to create a table in a stored procedure.
Using CTEs, for instance, you can use SELECT from <subquery> in Open SQL. In my case I needed to execute dynamic SELECT count( DISTINCT col1, col2, …) which is not possible in the regular OpenSQL.
To help guard against SQL injection, I normally try to use functions wherever possible. In this case, you could do:
... SET @TableName = '<[db].><[schema].>tblEmployees' SET @TableID = OBJECT_ID(TableName) --won't resolve if malformed/injected. ... SET @SQLQuery = 'SELECT * FROM ' + OBJECT_NAME(@TableID) + ' WHERE EmployeeID = @EmpID' If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
