Assigning variable to a tablename in a St Proc

[Toga]

I have several tables I want to use a stored procedure with but I'm having trouble assigning the table name to a varialble, and then using in a select statement.

This does not work....What's the proper syntax?

Declare @TblNme varchar(25)
select @TblNme = 'MyTable'

Select * From @TblNme

Sybase just says Incorrect syntax near '@DataTbl'

 

[JMCraig]

Toga,

The bad new is, you can't use variables to represent table or column names in Sybase. The reason for this is due to the compilation approach that is used in Sybase. A statement must compile before it can be executed (and this happens ahead of time with a stored procedure). One of the things that happens during compilation is that identifiers that refer to DB objects are resolved from names to id's--which clearly can't happen if the indentifier is a variable. So, in Transact-SQL, you cannot use a variable to stand for a table or column name.

What you can do is create a version of the stored procedure that works for each table of interest and then execute the proper one based on a condition in your Transact-SQL code. Or, you can write a program that drives the selection (but you probably didn't want to do that, right?).

Sorry to bear the bad news.

John

J M Craig
Alpha-G Consulting, LLC

http://www.alphagconsulting.com

nsjmcraig@netscape.net

 

[Toga]

Thanks John for the clarification. At least I have a bettr understanding now as to why I couldn't get anywehre with this.

I'm using Access as the front end software. What I ended up doing was going with a passthrough query as opposed to having a stored procedure out on the server. If I could have had one procedure out there, it would have been my preference. This seems to work pretty good thus far though......at least I don't have to drag all my data back to access before running my calculations, which was the main thing I needed to avoid.

Again...Many Thanks.

 

[tinkuK]

Hi
Yes there is a way to make it work
we have dynamic SQL feature provided by sybase

what you have to do is, assign the whole select statement
to a string variable say @exe_str

select @exe_str ="Select * From "+@TblNme

Then use this command to execute the statement in @exe_str
exec (@exe_str)

Thats it

Gopal