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
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----
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
--------------------------
ASKER
ChirsKing-san
Thank you very much for the quick reply.
I have tested it and it WORKED ^_^
with the best regards
soy
Thank you very much for the quick reply.
I have tested it and it WORKED ^_^
with the best regards
soy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am first time user. sorry for the unclosed message.
^_^
^_^
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
+ ' WHERE a.altsuid=u.suid and a.suid=l.suid'
exec( @sql )
--Fetch cursor info to variable
fetch cur into @dbname
end