CODEWITHSUNDEEP
sql-serverselectstored-proceduresinput-parameters
user3713775
user3713775

Reputation: 168

Table Name Variable in select statement in sql server

I want to use tablename variable in select statement, but it is giving error

- Must declare the table variable "@table" 

alter PROCEDURE testTblName
(
@id int,
@name varchar(50)
)
AS
BEGIN
   declare @table varchar(50)
   declare @add varchar(150)
   set @table = 'DBTest..testMaster'
   select @add = address from @table where id=@id and name=@name
END

This is a snap shot of my code

Upvotes: 1

Views: 1632

Answers (3)

Radu Porumb
Radu Porumb

Reputation: 785

You can't use a variable for your table name. When you do select address from @table SQL expects @table to be a variable of the table type, not a scalar.

You're looking to do something like this:

ALTER PROCEDURE testTblName
(
    @id INT,
    @name VARCHAR(50)
)
AS
BEGIN
    DECLARE @table VARCHAR(50),
            @add VARCHAR(150),
            @params VARCHAR(200),
            @sql VARCHAR(200);

    SET @params = '@add VARCHAR(50) OUTPUT';

    SET @table = 'DBTest..testMaster'

    SET @sql = 'SELECT @add = address FROM ' + @table + ' WHERE id=' + @id + ' AND name=' + @name

    EXEC sp_executesql @sql, @params, @add OUTPUT;

    ...
    ...
END

Upvotes: 2

Jithin Shaji
Jithin Shaji

Reputation: 6073

I think, u will have to give something like this.

alter PROCEDURE testTblName
(
@id int,
@name varchar(50)
)
AS
BEGIN
    declare @table varchar(50)
    declare @add varchar(Max)
    set @table = 'DBTest..testMaster'

    Set  @add = 'Select address from ' + @table + ' where id = ' + CAST(@id AS VARCHAR(10)) + ' and name = ' + @name

    Exec(@add)
END

Upvotes: 0

Dave.Gugg
Dave.Gugg

Reputation: 6781

You need to use dynamic sql for this:

alter PROCEDURE testTblName
(
@id int,
@name varchar(50)
)
AS
BEGIN
   declare @table varchar(50)
   declare @add varchar(150)
   set @table = 'DBTest..testMaster'
   DECLARE @sql NVARCHAR(MAX)
   SELECT @sql = 'select @add = address from ' + @table + ' where id= ' + @id + ' and name= ' + @name
   EXEC sp_executesql @sql

END

Upvotes: 3

Related Questions