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
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
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
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
