Creating a stored procedure

[Jim]

Im trying to create a stored procedure that selects everything from a
function name that im passing in through a parameter..

create procedure SP_selectall
(@functionname varchar(25))
as

select * from @functioname

go

I keep getting this error:

Server: Msg 137, Level 15, State 2, Procedure SP_selectall, Line 5
Must declare the variable '@functioname'.

Whats the issue?

[Simon Hayes]

"Jim" <ji********@motorola.com> wrote in message
news:72*************************@posting.google.co m...

Im trying to create a stored procedure that selects everything from a
function name that im passing in through a parameter..

create procedure SP_selectall
(@functionname varchar(25))
as

select * from @functioname

go

I keep getting this error:

Server: Msg 137, Level 15, State 2, Procedure SP_selectall, Line 5
Must declare the variable '@functioname'.

Whats the issue?

You could do this with dynamic SQL (see below), but that's probably not a
good idea. What happens if some functions require parameters and some don't?
If you don't know the function name ahead of time, then you don't know the
form of the result set, so you may have difficulties handling it on the
client side. Performance could be a problem too, if any of the functions are
complex. For a detailed discussion of dynamic SQL, see here:

http://www.algonet.se/~sommar/dynamic_sql.html

But having said all that, if you have a good reason, and if you're
completely sure that you will never write a function requiring parameters,
then this should work:

create proc dbo.SelectAllFromFunction
@FunctionName sysname
as
set nocount on
begin
exec('select * from dbo.' + @FunctionName + '()')
end

This is for table-valued functions only, of course. You shouldn't use sp_ as
the prefix for your stored procs, by the way - that's used for system procs,
and the name resolution is different for them.

Simon