Can variables be declared in an Inl

[AccessSQLUser]

Can variables be declared in an Inline Table-Valued function?
What I want to do is create a function that accepts a parameter called "DatabaseName" and then it will return a set of records from a table in that database.
CREATE FUNCTION UserStoredProcedures
(@DatabaseName sqlvariant)
RETURNS table
AS

RETURN (
SELECT name
FROM @DatabaseName..sysobjects
WHERE type = 'P' and status >= 0
)
But this doesn't work obviously but I can't put the statement in a string and then execute it either?
What do you suggest?

 

[Cagliostro]

declare @xxx as varchar(...)
set @xxx = 'SELECT name from '
set @xxx = @xxx + @databasename + '..sometableWHERE type = 'P' and status >= 0'
return exec @xxx
John Fill

ivfmd@mail.md

 

[AccessSQLUser]

Thanks. I thought I tried it before but now I realized that I left out the AS keyword when declaring the variables.
It was just a silly oversight on my part.

 

[AccessSQLUser]

Actually, it worked only when I was testing it as a SQL statement outside the Function. But when I try to create a function it doesn't work. I get an error "Incorrect syntax near the keyword 'declare'."
Here's my code:
ALTER FUNCTION UserStoredProcedures
(@DatabaseName sqlvariant)
RETURNS table
AS
declare @myString as varchar(125)

set @databaseName='AAAViperTest'
set @myString = 'SELECT name
FROM ' + @DatabaseName+'..sysobjects
WHERE type = ''P'' and status >= 0 '


RETURN (
exec (@mystring)
)

 

[Cagliostro]

Keyword AS is my mistake. You shouldn't put it. John Fill

ivfmd@mail.md

 

[AccessSQLUser]

but I still get the error

 

[Cagliostro]

Below is my variant:

create procedure UserStoredProcedures
(@DatabaseName char(100))
AS
declare @myString varchar(125)

set @databaseName='AAAViperTest'
set @myString = 'SELECT name
FROM ' + @DatabaseName+'..sysobjects
WHERE type = ''P'' and status >= 0 '


RETURN exec @mystring
John Fill

ivfmd@mail.md

 

[AccessSQLUser]

I know that it works in a stored procedure. I was just wondering if it works in a function.

 

[Cagliostro]

Change it to function. I work with SQL7 and do not know how to do it. John Fill

ivfmd@mail.md