Solved

how can I use variable within the FROM statement.

Posted on 2003-11-24
4
987 Views
Last Modified: 2008-02-01
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
----------------------------------------------------------------------------------------------------
0
Comment
Question by:soykawasaki
  • 2
  • 2
4 Comments
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9815730
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
0
 

Author Comment

by:soykawasaki
ID: 9815892
ChirsKing-san
Thank you very much for the quick reply.
I have tested it and it WORKED ^_^
with the best regards
soy
0
 
LVL 6

Accepted Solution

by:
ChrisKing earned 500 total points
ID: 9816680
don't forget, you need to close the question .... and assign points :)
0
 

Author Comment

by:soykawasaki
ID: 9816718
I am first time user. sorry for the unclosed message.
^_^
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This tutorial shows how to create a greeting card by combining two image layers and a text layer on a PC using a free image editing app.
There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question