Hi,
In a SP, I want to switch table names based on an input parameter. I am afraid of dynamically building the entire SQL because of SQL Injection problems. Any suggestions on how to achieve this ?

You can pass in the table name or just a number which you transalate and just use it for the table.
This checks for the existance of the table name passed in so should be reasonable safe. Will still need permission on the table though.

Would prefer a series of if statements and hard coded queries.

@tblname varchar(128)
as

if not exists (select * from sysobjects where name = @tblname)
begin
raiserror(invalid name, 16, -1)
return
end

declare @sql varchar(3000)
select @sql = 'select col, col from <tbl> where col = ''asdf'''
select @sql = replace(@sql, '<tbl',@tblname)
exec (@sql)
go

The question indicates that you are mixing data with metadata. Table
names shouldn't carry information in an ideal database. If a redesign
is impossible, is it possible to put the data where it belongs, in a
column (of a view)?

create view Better as
select
'ThisTable' as someData,
columnA,
columnB, ...
from ThisTable
union all
select
'ThatTable',
columnA,
columnB, ...
....
go

Then the sp can be written as

select ...
from Better
where someData = @tableName

Steve Kass
Drew University

[quoted text, click to view]