How to pass a parameter to a view?

Hi Everyone,

Is there an easy way that I could pass a filter (variable) unto a view
via stored procedures? I would appreciate your suggestions.

Benito

"Benito" <benito111@juno.com> wrote in message
news:5c26dce7.0310150725.25a9789b@posting.google.c om...[color=blue]
> Hi Everyone,
>
> Is there an easy way that I could pass a filter (variable) unto a view
> via stored procedures? I would appreciate your suggestions.
>
> Benito[/color]

Views cannot take parameters, but if you are using MSSQL 2000, then a
table-valued UDF is one solution:

create function dbo.MyFunc (@parm int)
returns table
as
return (
select col1, col2
from dbo.MyTable
where idcol > @parm
)

select col1, col2 from dbo.MyFunc(5)

There are examples in Books Online. If you have an earlier version of MSSQL,
then you'll have to use a stored procedure.

Simon

>> Is there an easy way that I could pass a filter (variable) unto a
view
via stored procedures? <<

VIEWs are virtual tables; they do not have parameters. Stored
procedures do take parameters and can return a resultset.

Simon,

Thanks for the suggestion. I am using MSSQL 2000. When creating a new
view at Enterprise Manager, I don't see the function I created at the
function tab of the Add Table Dialog Box. What I am trying to do is
store the filter in the function in the form of a variable then link
it to an existing view. Is this possible?

Benito

"Simon Hayes" <sql@hayes.ch> wrote in message news:<3f8d9385_4@news.bluewin.ch>...[color=blue]
> "Benito" <benito111@juno.com> wrote in message
> news:5c26dce7.0310150725.25a9789b@posting.google.c om...[color=green]
> > Hi Everyone,
> >
> > Is there an easy way that I could pass a filter (variable) unto a view
> > via stored procedures? I would appreciate your suggestions.
> >
> > Benito[/color]
>
> Views cannot take parameters, but if you are using MSSQL 2000, then a
> table-valued UDF is one solution:
>
> create function dbo.MyFunc (@parm int)
> returns table
> as
> return (
> select col1, col2
> from dbo.MyTable
> where idcol > @parm
> )
>
> select col1, col2 from dbo.MyFunc(5)
>
> There are examples in Books Online. If you have an earlier version of MSSQL,
> then you'll have to use a stored procedure.
>
> Simon[/color]