November 19, 2001 at 1:54 am
Hi,
is it possible to select data from a table not specified by compile-time in T-SQL? SOmething like SELECT * FROM @table.
I get an error message every time i try to.
I would appreciate any help.
->Flo
November 19, 2001 at 2:56 am
You could construct the slq text as a string and use: EXEC("sqlstring") or sp_Executesql.
Alternatively you could use a user defined function, which can be used in l=place of a tablename in a sql statement.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
November 19, 2001 at 4:57 am
Thank you, exec(@query) works. But why doesn't it work with the variable in the from clause?
->Flo
November 19, 2001 at 5:18 am
I guess it's because @table will be interpreted as the name of a table itself, rather than a variable holding the name. So if you didn't have a table called @name then the parser will complain as it can't create a query plan.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
November 19, 2001 at 5:20 am
May be, but "Select @column from Table" is accepted...
->Flo
November 19, 2001 at 5:31 am
Interesting - never thought of this as a discrepancy before. However, I suppose there is a distinction. In the case of "Select @column, col1 from Table" the @column is not really a column. It holds a literal value assigned before the execution of the sql. This is shown be the fact that the query plan is unaltered between
Select @column, col1 from Table
and
Select col1 from Table
If you want @column to be a column name you still have to concatenate the text and use exec(sql).
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
November 19, 2001 at 5:40 am
Sounds reasonable, well, i have thought of another reason, what do you think:
A sp is compiled at creation, isn't it? So, could it be that the compiler complains because it has to "hard-wire" the Tables in the query plan for efficiency reasons?
->Flo
November 19, 2001 at 5:59 am
>> A sp is compiled at creation, isn't it
Not any more - it is compiled at first run - and possibly at every run if it is dependent on transient objects.
select * from @table
select @col, col1 from table
These are different things - the first is trying to access a object in a variable - the second is just outputting a literal in a variable.
I guess there is no reason now why the server couldn't replace @table with a variable name when it compile the SP - after all it can do this with temp tables and generate the query plan on the fly - even if the temp table structure changes during execution but the facility isn't available and you just have to live with it.
With 6.5 the objects were resolved when the SP was compiled so this wouldn't have been possible (imho a much better arrangement as you could detect missing objects before running the SP).
Cursors never.
DTS - only when needed and never to control.
November 19, 2001 at 10:03 am
In SQL 2000, you can do this:
declare @t table (myid int)
insert into @t select 1
select * from @t
The reason that you cannot specify a table is that the parser is expecting a table like structure from the target of the FROM clause. You variable is not a table, but in fact a variable. In sql 2000, you can do this by making this a table.
Keep in mind, this is not a placeholder, but in fact an object that is acted on. The column list allows columns or specific vales, like select 1 which is what your select @column is. You cannot do a select * from 1, which is what you are trying to do.
Steve Jones
November 20, 2001 at 12:43 am
I have a problem with the execute() statement. My sql string is correct, but if i execute it with exec() then the stored procedure returns, if it is a select query otherwise not, without executing the following statements.
For example:
declare @sql nvarchar(100)
set @sql='Select * into #temp_copy From ' + @table
exec ( @sql )
/*here it returns*/
/*the following code is never executed
set @sql='drop table ' + @table
exec ( @sql )
Does anyone know how to solve the problem?
By the way, i am using SQLServer v7.0
->Flo
November 21, 2001 at 8:06 am
If you're on SQL 2000 I'd use Steve's solution - the table variable - as it's easier and doesn't need any clearing up of a temporary table. Also, the temp table will be deleted when the connection is dropped so you might not need any more code anyway.
Finally, if you do decide to use the table variable, BOL is not too clear about this but SQl Server Magazine explains that if you need to do a join, you must use an alias to get it to work.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
November 21, 2001 at 10:11 am
I ran this on SQL 2000
create table MyTest ( MyID int, MyChar varchar(20) )
go
insert MyTest select 1, 'A'
go
select * from MyTest
go
create procedure myProc
@tbl char( 10)
as
declare @cmd varchar( 100)
select @cmd = 'drop table ' + @tbl
select @cmd
exec( @cmd)
return
go
exec MyPRoc 'MyTest'
go
drop procedure myProc
go
It works. If I run the create and insert above and then run this:
declare @sql nvarchar(100), @table nvarchar( 100)
select @table = 'MyTest'
set @sql='Select * into #temp_copy From ' + @table
exec ( @sql )
/*here it returns*/
--the following code is never executed
set @sql='drop table ' + @table
exec ( @sql )
select * from MyTest
I get an error from the last statement as the table has been dropped. One thing, you have an unclosed comment on your second comment line. Also, the temp table is created within the space of the exec() statement and is not accessable from your connection.
Steve Jones
November 22, 2001 at 12:56 am
OK, thanks a lot, thats it. I didn't know that a temporary table created within an exec statement, isn't accessable from my connection. So i have to use a global temporary table i guess.... right, i have too. Have Just tried it, it works like it is suposed to be. I wrote a stored procedure which drops an entire column from a table, the table, column and the db are arguments to the procedure.
->Flo
November 22, 2001 at 5:04 am
A temp table crated in an exec statement only has scope of that batch.
You can create a temp table with a dummy column, call the exec to alter the table and populate it then it will be accessible outside.
You can also use openquery with the exec as an argument to a select into to create the table.
Cursors never.
DTS - only when needed and never to control.
November 22, 2001 at 5:32 am
i am, as you have probably noticed, a T-SQL novice. I looked up the microsoft OPENQUERY dokumentation, but its not quite clear to me how to use it in this case. Could you please give me an example and explain it?
Thanks,
->Flo
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply