CODEWITHSUNDEEP
sqlt-sql
Jin Yong
Jin Yong

Reputation: 43768

How to make table dynamic in sql

Does anyone know how to write a script in stored proc to run the table based on the variable (or will it possible to do so?)?

for example: I have 3 tables name called customer, supplier, and support

when user input 1, then run table customer, 2 table supplier and 3 table support

declare @input int;

if @input =1
begin
declare @table varchar(50); set @table = 'customer'
end

if @input =2
begin
declare @table varchar(50); set @table = 'supplier '
end

if @input =3
begin
declare @table varchar(50); set @table = 'support'
end 

select *
INTO ##test
from  @table

Upvotes: 1

Views: 113

Answers (3)

Pranay Rana
Pranay Rana

Reputation: 176886

yes you can do it by using dynamic sql "EXEC" or by "Sp_Executesql" command.

Example : 

USE Northwind
GO


CREATE TABLE #MyTemp
   (  RowID    int  IDENTITY,
      LastName varchar(20)
   )

DECLARE @SQL nvarchar(250)
SET @SQL = 'INSERT INTO #MyTemp SELECT LastName FROM Employees;'
EXECUTE sp_executesql @SQL

Upvotes: 0

Abe Miessler
Abe Miessler

Reputation: 85036

Why do you want to do this? It seems like a bad idea at first glance.

Can you post what your stored procedure is doing and any relevant tables? I suspect that you may be able to either:

  1. Modify your schema in such a way that you would no longer to do this
  2. Create different stored procedures to do what you want on each table instead of forcing it into one proc.

There are several issues that come up when you use dynamic SQL that you should be aware of. Here is a fairly comprehensive article on the pros and cons.

Upvotes: 0

Bill
Bill

Reputation: 4585

IF it really is that simple, why not just repeat the Select?

if @input =1
begin
Select * INTO ##test From customer
end

if @input =2
begin
Select * INTO ##test From supplier
end

if @input =3
begin
Select * INTO ##test From support
end

Upvotes: 1

Related Questions