I want to make a vairable of table can i make it on Microsoft sql server management studio?

Question

I want to make a variable of table in stored procedure to call the data from 3 diffrent tables like i have the city name in textbox and a dropdown list with the values of tablenames,

I already make a stored procedure:

[dbo].[sp_Search]
@City nvarchar(50) = null
AS
SELECT * FROM PropertyForSale_TBL  WHERE (City =    @City OR @City IS NULL)

here this is the SP of only for the textbox not for the dropdownlist.

i want a procedure to make the tablename as a variable so when i select the dropdown it calls the data from that table. like as i imagined its works like this maybe....

[dbo].[sp_Search]
@City nvarchar(50) = null,
@Tablename Table (maybe or maybe not i dont know it is not working)

AS
SELECT * FROM @Tablename  WHERE (City = @City OR @City IS NULL)

how can i do this?????

Try declaring a variable /temporary table with its structure,just like how you would create a new table. Later you could insert, query into that table. — Dilip, May 02 '18 at 09:00

PSK · Accepted Answer · 2018-05-02T09:26:40.290

You need to build a dynamic query as the table name can't be a variable. For this case you can use sp_executesql to run the dynamic query as shown below.

You can make it parameterized query using sp_executesql to avoid any SQL Injection threat.

create procedure [dbo].[sp_search] 
   @City      nvarchar(50) = null, 
   @Tablename varchar(100) 
as 
  begin 
      declare @sql nvarchar(max)
      set @sql = N'SELECT * FROM ' +  quotename(@Tablename) 
             + '  WHERE (City = @City OR @City IS NULL)' 
      exec sp_executesql 
        @sql, 
        N'@City nvarchar(50)',       
        @City 
  end

score 0 · Answer 2 · answered May 02 '18 at 09:08

You can build dynamic SQL and execute it

Here's a quick example (note there are multiple ways to execute dynamic SQL and each have their own benefits and draw-backs, do a little research)

DECLARE @City       NVARCHAR(50) = 'New York'
      , @TableName  VARCHAR(100) = 'PropertyForSale_TBL'


DECLARE @SQL NVARCHAR(MAX) = 
    'SELECT * FROM ' + QUOTENAME(@Tablename) + ' WHERE [City] = ''' + @City + ''''

PRINT @SQL -- SELECT * FROM [PropertyForSale_TBL] WHERE [City] = 'New York'

EXEC (@SQL)

Edit: Also just a note dynamic code is usually not the best idea. The above opens yourself up to SQL injection and other headaches. Have a look at your database structure and see whether the data can be put in the same tables, IF statements and temp tables used, or multiple stored procedures.

I want to make a vairable of table can i make it on Microsoft sql server management studio?

2 Answers2