Constructing the FROM in SQL

Question

I'm looking to pull a specific line from a number of table that have a field name criteria1. The problem I'm having is that when I combine the Owner and Table Name and try to call "select criteria1 from @t where linenum = 1" SQL is expecting @t to be a table. I need to know how to construct the full table name and then pass it to this query. I know I can us a programming language to access the DB but i need this to be in SQL. If someone knows of a better way of doing this that would be great too.

declare @next as varchar
declare @owner varchar

while 1=1
begin
  set @next = (select top 1 o.name FROM syscolumns c inner join sysobjects o on c.id = o.id 
               where c.name = 'criteria1' and o.id > @next order by o.id)    

  if @next is null
    break
  else
  begin
    set @owner = (select top 1 u.name 
         FROM syscolumns c inner join 
           sysobjects o on c.id = o.id left join 
           sysusers u on o.uid=u.uid
         where c.name = 'criteria1' and o.id = @next order by o.id)
    declare @t as varchar
    set @t = @owner+'.'+@next
    select criteria1 from @t where linenum = 1  
  end
  continue
end

Answer 1

You can build the entire query you want as a varchar() and then execute it with the sp_executesql stored procedure.

http://msdn.microsoft.com/en-us/library/ms188001.aspx

In your case, that bit at the end becomes

declare @sql varchar(512);
set @sql = 'select criteria1 from ' + @t + ' where linenum = 1'          
sp_executesql @sql

Answer 2

Have you considered the following construct in a stored procedure?

  CASE @tablename
     WHEN 'table1' THEN SELECT * FROM table1
     WHEN 'table2' THEN SELECT * FROM table2
     WHEN 'table3' THEN SELECT * FROM table3
     WHEN 'table4' THEN SELECT * FROM table4
  END

In case you're married to dynamic SQL (considered to be a bad choice for this problem space), this guide to dynamic SQL should help a lot. It helped me and I've used dynamic SQL extensively.

Answer 3

Maybe a view can be used here?

CREATE VIEW vCriterias
AS
SELECT 'Table1' AS TableName,
linenum,
criteria1
FROM Table1
UNION ALL
SELECT 'Table2' AS TableName,
linenum,
criteria1
FROM Table2
UNION ALL
SELECT 'Table3' AS TableName,
linenum,
criteria1
FROM Table3

go

Then selection is like:

   SELECT criteria1
   FROM vCriterias
   WHERE linenum = 3 
   AND TableName IN ('Table1','Table3')

Answer 4

Thanks for all the help. This is what I ended up with.

declare cur cursor for
select   u.name + '.' + o.name tname
 FROM sysobject o left join 
   syscolumns c  on c.id = o.id left join 
   sysusers u on o.uid=u.uid
 where c.name = 'criteria1'

declare @tn as varchar(512)
open cur
fetch next from cur into @tn

create table holding_table ( val varchar(512), table_name varchar(512))
declare @sql nvarchar(1000)

while @@FETCH_STATUS = 0
begin
 set @sql = 'insert into holding_table select criteria1, ''' + @tn + ''' from ' + @tn + ' where linenum = 1'
 execute sp_executesql @sql 
 fetch next from cur into @tn 
end
close cur
deallocate cur