Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select statement using variable values - Why doesn't this work?

Status
Not open for further replies.

mayB2morrow

Programmer
Joined
Sep 3, 2004
Messages
10
Location
GB
Can anyone tell me why the following SQL statement doesn't work? I get an error telling me I must declare "@TableSelect".

And can anyone tell me how to change this code so that it does work?!

-------------------------------

Declare @adminuser varchar(20)
Set @adminuser = 'admin'

Declare @TableSelect varchar(200)
Set @TableSelect = @adminuser + '.Customers'

select * from @TableSelect

--------------------------------

Thanks!

Phil Marsay
 
Since @TableSelect is a variable and not a table, you cannot select * from @TableSelect.

However, changing your last statement to:

select @TableSelect as 'TableSelect'

Will result in a row called TableSelect with the output "admin.customers".

Good luck!

Lara
 
Lara, I know I can do this, but I really need to build the select statement where the table name comes from a variable. Is there not anyway that i can do this?

Thanks.
Phil.
 
Phil,

Please can you clarify what you mean as the table name is coming from a variable in Lara's example

Jon
 
Well, as I said in my original post, I need to be able to do:


select * from @variable
where...


or, something which will give the same results.

Lara's suggestion returns me the value of the variable in a resultset which is not what I need to do.
 
Phil,

A variable can only hold one value at a time, meaning there are not multiple rows to filter in a where clause.

The code you posted would only ever store 'admin.Customers' as it is a hardcoded string.

If you wish to store multiple values then you need either a table variable or a temp table.

Jon
 
yes, I know it is harcoded, but the value for @TableSelect will be used many times in a large script.

I'd like to be able to change the variable at the top of the script and have it reflect all the way down the script without having to go through and do a "find and replace". I know this sounds a little lazy, but there are other reasons - this script may be passed to other people to run - people not as technical as myself.

Telling them to change one value at the top of a page is much simpler than saying they need to go through the script and do a find and replace.

Besides, it's more the concept of whether it can be done that I am interested in as I am likely to use something similar in other areas.

Thanks.
 
THis topic has been covered quite a bit (passing table or column names into a select statement via variables) and the resounding feelings are..

1. Yes can be done via the execute command
2. This can cause all sorts of unanticipated security problmes as the use of Exec inside a storedproc causes SQL to check permissions for the user executing it on every object covered by the dynamic sql
3. Big casestatemens inside the proc might be a better option.
4. If you really really really really need to do this....

Code:
Create Proc QueryAnyTwoColumnsFromSomeNorthwindTable
@tableName varchar(300),
@Col1 varchar(300),
@col2 varchar(300)
as
set nocount on
declare @SQL varchar(300)
set @SQL = 'select ' + @Col1 + ',' + @Col2 + ' from northwind.dbo.' + @tablename + ' Order by 1'
exec (@SQl)
go
QueryAnyTwoColumnsFromSomeNorthwindTable 'employees','firstname','lastname'


Rob

PS my advise... try another way!


 
I agree with Rob. Yes you can do this, no you shouldn't unless you are creating a stored procedure for only admins to use.

Not only are there security problems, but this kind of code is impossible to totally debug because you can't know what will be input to actually create the select statment.

[RANT]If you are expecting users to use this and are really using select *, then you are deliberating making a system that will be clogged and slow becasue you will often be returning more information than is necessary for a particular situation. Many people write this kind of code for frequent reuse, but code should never be reused when it makes the system more inefficient. It is far better to write 500 stored procedures that do exactly what is needed than one all-purpose sp like this.[/RANT}

Questions about posting. See faq183-874
 
ugh, can't believe it is that complicated!!

Will have to rethink things a little!

BTW - SQLSister - apreciate what you are saying with the rant but I do know this, I just said select * as an example.

Thanks for everyones comments.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top