Table name a String
-
Hi All i have a sp which to querys a database, but each time the sp is run the name of one of the tables changes, so i want to pass the table name into the query as a string something like DECLARE @table_name varchar(100) set @table_name='mytable' exec ('select my_id from ' + @table_name+' ORDER BY my_ID') which works fine when im only using one table i wanted to do a join across several tables, and do something like set @sql_stmt = 'select callingnumber,seizedatetime,callednumber,new_action.telephone,new_action.action1,new_action.action2, new_call.student_id, datepart(dd,seizedatetime) as myday, null as sent_it from new_call,new_action,'+@table_name+' where new_call.call_ID=new_action.call_id' EXEC ( @sql_stmt ) but that doesnt work does ne1 have any ideas :~ :confused::confused:
-
Hi All i have a sp which to querys a database, but each time the sp is run the name of one of the tables changes, so i want to pass the table name into the query as a string something like DECLARE @table_name varchar(100) set @table_name='mytable' exec ('select my_id from ' + @table_name+' ORDER BY my_ID') which works fine when im only using one table i wanted to do a join across several tables, and do something like set @sql_stmt = 'select callingnumber,seizedatetime,callednumber,new_action.telephone,new_action.action1,new_action.action2, new_call.student_id, datepart(dd,seizedatetime) as myday, null as sent_it from new_call,new_action,'+@table_name+' where new_call.call_ID=new_action.call_id' EXEC ( @sql_stmt ) but that doesnt work does ne1 have any ideas :~ :confused::confused:
-
Hi All i have a sp which to querys a database, but each time the sp is run the name of one of the tables changes, so i want to pass the table name into the query as a string something like DECLARE @table_name varchar(100) set @table_name='mytable' exec ('select my_id from ' + @table_name+' ORDER BY my_ID') which works fine when im only using one table i wanted to do a join across several tables, and do something like set @sql_stmt = 'select callingnumber,seizedatetime,callednumber,new_action.telephone,new_action.action1,new_action.action2, new_call.student_id, datepart(dd,seizedatetime) as myday, null as sent_it from new_call,new_action,'+@table_name+' where new_call.call_ID=new_action.call_id' EXEC ( @sql_stmt ) but that doesnt work does ne1 have any ideas :~ :confused::confused:
Looks to me like you forget to put a statement in the WHERE clause to join @table_name to either new_call or new_action. I can't be sure, but it also looks like you may have a whitespace problem. Are you actually getting an error message, or are just just not getting the results you expected?
Grim
(aka Toby)
MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL GO
(0 row(s) affected)