Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

how can I use variable within the FROM statement.

Posted on 2003-11-24
4
Medium Priority
?
1,032 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

What monsters are hiding in your child's room? In this article I will share with you a tech horror story that could happen to anyone, along with some tips on how you can prevent it from happening to you.
If something goes wrong with Exchange, your IT resources are in trouble.All Exchange server migration processes are not designed to be identical and though migrating email from on-premises Exchange mailbox to Cloud’s Office 365 is relatively simple…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

647 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