Link to home
Start Free TrialLog in
Avatar of soykawasaki
soykawasaki

asked on

how can I use variable within the FROM statement.

Greeting experts!! ^_^
I am working on a store procedure within the sybase DB.
The code that I wrote below is a store procedure.
I am trying to retrieve a database name from sysdatabase (system table) and retrieve sysusers and sysalternates information from each database within the system.
When I try to run the program. I get a error saying "Error: Incorrect syntax near '@dbname'.
 (State:37000, Native Code: 66)" I was told from buddy that if I want to retrieve informations within all the DB. I have to use perl to do so. If there are way to work this out without perl, it will be a great help.
Cordially
SOY

----------------------------------------------------------------------------------------------------
--Create procedure
create procedure sp_altlist as

--Declaring variable
declare @dbname sysname(30)

--Declaring cursor
declare cur cursor for
select name from master..sysdatabases

--Open cursor
open cur

--Fetch cursor info to variable
fetch cur into @dbname

--Loop
while (@@sqlstatus = 0) --if complete
begin
   SELECT @dbname, l.name, alt_name=u.name
   FROM @dbname..sysalternates a, @dbname..sysusers u, master..syslogins l
   WHERE a.altsuid=u.suid and a.suid=l.suid
   --Fetch cursor info to variable
   fetch cur into @dbname
end

--Close cursor
close cur
deallocate cursor cur

go
----------------------------------------------------------------------------------------------------
Avatar of ChrisKing
ChrisKing

you need to use dynamic sql (of course depends on the version of ASE your are running)

change the loop to the following section of code

DECLARE @sql varchar(254)
--Loop
while (@@sqlstatus = 0) --if complete
begin
   SELECT @sql  = 'SELECT '''+@dbname+''', l.name, alt_name=u.name '
   +   ' FROM '+@dbname+'..sysalternates a, '+@dbname+'..sysusers u, master..syslogins l '
   +   ' WHERE a.altsuid=u.suid and a.suid=l.suid'

   exec( @sql )

   --Fetch cursor info to variable
   fetch cur into @dbname
end
Avatar of soykawasaki

ASKER

ChirsKing-san
Thank you very much for the quick reply.
I have tested it and it WORKED ^_^
with the best regards
soy
ASKER CERTIFIED SOLUTION
Avatar of ChrisKing
ChrisKing

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am first time user. sorry for the unclosed message.
^_^