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 TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Selecting from a table dynamically

Status
Not open for further replies.

nkm

Programmer
May 27, 2001
45
US
Hi

Can we do this

declare @tabName char(30)
select @tabName="CreditUser"
select * from @tabName

I want the table name to be determined at run time

If not what is the alternative
regards


 
Hi

Can't be done the way you want to do it, BUT, all is not lost.

Try using the "exec" command.

Code:
declare 
    @tabName char(30),
    @selectClause varchar(255)


SELECT @tabName="CreditUser"
SELECT @selectClause = "SELECT * FROM " + @tabName + " "

exec( @selectClause )
[code][COLOR=black]

Some things you should know.
[OL]
[LI]variables of type varchar(x) can be larger than 255 if you are using larger page sizes. (The 'norm' is 255 though)
[LI]a command executed inside 'EXEC' can be longer then 255 chars.
[LI]Break your sql statement up into "selectClause, FromClause, WhereClause etc etc" if you need to create large statements over 255 chars.
[/OL]
Ex:
[COLOR=purple][code]declare 
    @tabName char(30),
    @selectClause varchar(255),
    @fromClause varchar(255),
    @whereClause varchar(255)

SELECT @tabName="CreditUser"

SELECT @selectClause = "SELECT * "
SELECT @fromClause = "FROM " + @tabName + " "
SELECT @whereClause = "WHERE blah = blah"

exec( @selectClause + @fromClause + @whereClause )
[code][COLOR=black]


Hope this helps.

-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and [CODE]
[/ignore] tags.

Ex:
Code:
SELECT 1 from sysobjects

 
Hi

I tried the exec command but it comes back saying

The name <SQL> is not a valid identifier.
I guess exec expects a stored procedure. How do I overcome this ...

thanks
 
Actually you don't 'need' a stored procedure for exec.

What version of sybase are you using. (This may end up being irrelevant, but I just want to make sure).

In the meantime I'm going to look up that error for you.

Thanks.


-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and
Code:
[/ignore] tags.

Ex:
Code:
SELECT 1 from sysobjects

 
Just checked ans if you want ot &quot;Exec&quot; a T-SQL statement with &quot;exec&quot; you need to be running Version 12.0 ot up of Sybase ASE.

I hope this is the case...



-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and
Code:
[/ignore] tags.

Ex:
Code:
SELECT 1 from sysobjects

 
Hi

The version given using
select @@version

is

Adaptive Server Enterprise/11.5.1

regards
 
Well unfortunately you are out of luck trying to run dynamic SQL in that version of ASE (It's quite old).

If it's a feasible option I would upgrade to 12.5 but otherwise I am not sure how you would do what you want to do.

Sorry

-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and
Code:
[/ignore] tags.

Ex:
Code:
SELECT 1 from sysobjects

 
Found a solution for you.

Check here are Rob Verschoor's site (Sybase guru extraordinaire) :)



-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and
Code:
[/ignore] tags.

Ex:
Code:
SELECT 1 from sysobjects
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top