Solved

how can I use variable within the FROM statement.

Posted on 2003-11-24
4
1,006 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 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Microsoft is moving in-place eDiscovery & hold from ECP to EOP console under Content Search in Search and Investigation Options.  In this post, I will be showing you how to export emails to a PST file using the Content Search Options.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

688 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