Let's see... What I'm trying to do 1

[AccessSQLUser]

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.

 

[Tim1]

Hiya,

You need to do this as a dynamic SQL statement:

DECLARE @sql VARCHAR(255)
DECLARE @DBName VARCHAR(35)

SELECT a value to @DBName

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

EXEC (@sql)

HTH,

Tim