How to pass a parameter to a view?

[Benito]

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

[Simon Hayes]

"Benito" <be*******@juno .com> wrote in message
news:5c******** *************** ***@posting.goo gle.com...

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

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

[--CELKO--]

>> 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.

[Benito]

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" <sq*@hayes.ch > wrote in message news:<3f******* *@news.bluewin. ch>...

"Benito" <be*******@juno .com> wrote in message
news:5c******** *************** ***@posting.goo gle.com...

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

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