Can a variable-value be passed into a local variable? Could the local vairable then be used to build the Dynamic SQL query?

Question

I'm trying to pass user-inputted data to a SQL 'sp_executesql' (Dynamic SQL) statement in order to build a string for the 'SELECT','FROM', and 'WHERE' statements.

I know that SQL Server won't accept a table name or a column name as a parameter. However I was wondering if it was possible to take user-inputted values, store them in a locaL-SQL variable and then use the local variable in the 'FROM' clause?

I know this code would work:

set @tableName = 'SalesData'
set @monthNo = 2
set @sql = N'
 select SalesPerson 
 from ' + @tableName + '
 where mon = @monthNo'
exec sp_executesql @sql, N'@monthNo int', @monthNo

But, would this code run?

set @tableName = @ValueTypedByUser
set @monthNo = 2
set @sql = N'
 select SalesPerson 
 from ' + @tableName + '
 where mon = @monthNo'
exec sp_executesql @sql, N'@monthNo int', @monthNo

That basic approach will work, Rvaz, but I would be careful building things that open you to the potential of SQL injection. You can Google the risk it brings in doing so. Passing in table names and building dynamic SQL from that can be risky. There is, however, a reason why sp_executesql exists, so just be careful. You can certainly do what you are describing. — DanielG, Aug 26 '19 at 17:07

daShier · Answer 1 · 2019-08-27T20:00:02.693

How many tables are you possibly dealing with? If it's a small number you have two better choices:

Use multiple stored procedures instead of one, and call them based on the table you need. You can use a parameter in your calling routine to indicate which SP you want.
Use a parameter to specify the table you want, but then, instead of using a variable to change the table name in your SP, use the following conditionals:

   IF @table = 'SalesPersonTable'
       BEGIN
           SELECT SalesPerson 
           FROM SalesPersonTable
           WHERE mon = @monthNo
       END
   IF @table = 'OtherTable'
       BEGIN
           SELECT SalesPerson 
           FROM OtherTable
           WHERE mon = @monthNo
       END

This avoids the SQL injection issues, but again, only works if the number of tables is "small" (with "small" being what you want it to be!)

thanks daShier - i have 4 different tables to work with but since I'm new to SQL I'm going to use the second approach you mentioned! I'm on a time crunch and the 2nd approach seems more straightforward then having to figure out how to have storedproc call other storedprocs! thanks for your help! :) — rvaz, Aug 27 '19 at 19:45

Can a variable-value be passed into a local variable? Could the local vairable then be used to build the Dynamic SQL query?

1 Answers1