database variables in stored procedures 1

[AccessSQLUser]

(Woops - I forgot to put a subject in my other thread so that's why it didn't have a very descriptive header.)

Let's see... What I'm trying to do is create a function or stored procedure that will select all the user stored procedures in a database. I would want the procedure or function to accept a parameter called @DBName which would be the name of the database that should be used. Then it should do the following selection:

SELECT name
FROM @DBName.dbo.sysobjects
WHERE type = ''P'' and status >= 0

I know that this doesn't work because of the variable @DBName but I tried so many different ways to get it to work and I think I just about gave up?
Does anyone know if what I want to do is even possible?
Oh, and also I would like to be able to then refer to this procedure in some other procedure so that I can do further selections on it.

 

[tlbroadbent]

Create procedure MySP @dbname As

Declare @sql varchar(256)
Select @sql="Select * From " + @dbname + ".dbo.sysobjects where type='p'"
Execute(@sql) Terry

http://members.home.net/tlbroadbent/index.htm

 

[AccessSQLUser]

Here's my code now:

DECLARE @Temp table (ProcName varchar(250))
declare @mysql varchar (100)
Declare @DBName varchar(30)
set @DBName = 'AAAViperTest'

set @mysql=
'Insert @temp <I also tried 'Insert' + @temp + >
select name
from ' + @DBName +'.dbo.sysobjects
WHERE type = ''P'' and status >= 0'
print (@mysql)
exec (@mysql)

I get an error: &quot;Must declare the variable '@temp'.&quot; I know that the problem is that @temp is a table variable and not varchar but is there any way around it?
I would like to keep my code this way and not have to creat temp tables because I want to use this code in a function so that way it's simple to select records from the recordset that the function returns.

 

[tlbroadbent]

I think you are making it way too hard! You only need to create the stored procedure in the master DB...

Create procedure sp_ListProcedures @dbname varchar(40) As

Declare @sql varchar(256)
Select @sql=&quot;Select * From &quot; + @dbname +
&quot;.dbo.sysobjects where (type='p' And Status>0) &quot;
If @criteria<>Null
Begin
Select @sql=@sql + @criteria
End
Execute(@sql)


and then execute the SP from another procedure to return the list.

Exec sp_ListProcedures 'DBName'

Am I missing something in your requirements? Terry

http://members.home.net/tlbroadbent/index.htm