Selecting from a table dynamically

[nkm]

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

 

[JeanNiBee]

Hi

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

Try using the "exec" command.

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:

SELECT 1 from sysobjects

 

[nkm]

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

 

[JeanNiBee]

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

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[JeanNiBee]

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

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[nkm]

Hi

The version given using
select @@version

is

Adaptive Server Enterprise/11.5.1

regards

 

[JeanNiBee]

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

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[JeanNiBee]

Found a solution for you.

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

http://www.sypron.nl/dynsqlcis.html

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

[/ignore] tags.

Ex:

SELECT 1 from sysobjects