Parameter view

[Danilo Popoviæ]

Hi everybody,

this view runs just fine but when I try to save it I have an error message:
ODBC error: [Microsoft][ODBC SQL Server Driver]COUNT field incorrect of
syntax error. Where is the problem? I use questionmark because I need
unnamed parameter veiw.

select ProductID, NameOfProduct from dbo.Products where ProductID < ?

Thank you in advance,

Danilo Popoviæ, B. Sc.
Trgostil d.d. - IC
Toplièka 16
49240 Donja Stubica
Croatia

[Simon Hayes]

Re: Parameter view


"Danilo Popoviæ" <danilo.popovic @trgostil.hr> wrote in message
news:bjrmh1$3o0 $1@sunce.iskon. hr...[color=blue]
> Hi everybody,
>
> this view runs just fine but when I try to save it I have an error[/color]
message:[color=blue]
> ODBC error: [Microsoft][ODBC SQL Server Driver]COUNT field incorrect of
> syntax error. Where is the problem? I use questionmark because I need
> unnamed parameter veiw.
>
> select ProductID, NameOfProduct from dbo.Products where ProductID < ?
>
> Thank you in advance,
>
> Danilo Popoviæ, B. Sc.
> Trgostil d.d. - IC
> Toplièka 16
> 49240 Donja Stubica
> Croatia
>
>[/color]

You can't use parameters with a view. If you have MSSQL2000, then you could
use a table-valued function:

create function dbo.GetProducts (@ProductID int)
returns table
as
return (select ProductID, NameOfProduct from dbo.Products where ProductID <
@ProductID)

select * from dbo.GetProducts (5)

Otherwise, you can use a stored procedure.

Simon

[Danilo Popoviæ]

Re: Parameter view

Thank you. ;-)

Reagards,

Danilo Popoviæ, B. Sc.
Trgostil d.d. - IC
Toplièka 16
49240 Donja Stubica
"Simon Hayes" <sql@hayes.ch > je napisao u poruci interesnoj
grupi:3f6190fd_ 1@news.bluewin. ch...[color=blue]
>
> "Danilo Popoviæ" <danilo.popovic @trgostil.hr> wrote in message
> news:bjrmh1$3o0 $1@sunce.iskon. hr...[color=green]
> > Hi everybody,
> >
> > this view runs just fine but when I try to save it I have an error[/color]
> message:[color=green]
> > ODBC error: [Microsoft][ODBC SQL Server Driver]COUNT field incorrect of
> > syntax error. Where is the problem? I use questionmark because I need
> > unnamed parameter veiw.
> >
> > select ProductID, NameOfProduct from dbo.Products where ProductID < ?
> >
> > Thank you in advance,
> >
> > Danilo Popoviæ, B. Sc.
> > Trgostil d.d. - IC
> > Toplièka 16
> > 49240 Donja Stubica
> > Croatia
> >
> >[/color]
>
> You can't use parameters with a view. If you have MSSQL2000, then you[/color]
could[color=blue]
> use a table-valued function:
>
> create function dbo.GetProducts (@ProductID int)
> returns table
> as
> return (select ProductID, NameOfProduct from dbo.Products where ProductID[/color]
<[color=blue]
> @ProductID)
>
> select * from dbo.GetProducts (5)
>
> Otherwise, you can use a stored procedure.
>
> Simon
>
>[/color]

[Arun Marathe [MSFT]]

Re: Parameter view

And what Simon showed you is known as
"inline user-defined function". They give
you a functionality similar to parameterized
views. (Parameterized views are not
allowed in SQL Server 2000.)
--
Arun Marathe
Program Manager
SQL Server Query Processor
Microsoft Corporation

Disclaimers:
(1) This posting is provided "AS IS" with no warranties, and confers no
rights.
(2) Use of included script samples are subject to the terms specified at:

http://www.microsoft.com/info/cpyright.htm

"Danilo Popoviæ" <danilo.popovic @trgostil.hr> wrote in message
news:bjs40b$ej7 $1@sunce.iskon. hr...[color=blue]
> Thank you. ;-)
>
> Reagards,
>
> Danilo Popoviæ, B. Sc.
> Trgostil d.d. - IC
> Toplièka 16
> 49240 Donja Stubica
> "Simon Hayes" <sql@hayes.ch > je napisao u poruci interesnoj
> grupi:3f6190fd_ 1@news.bluewin. ch...[color=green]
> >
> > "Danilo Popoviæ" <danilo.popovic @trgostil.hr> wrote in message
> > news:bjrmh1$3o0 $1@sunce.iskon. hr...[color=darkred]
> > > Hi everybody,
> > >
> > > this view runs just fine but when I try to save it I have an error[/color]
> > message:[color=darkred]
> > > ODBC error: [Microsoft][ODBC SQL Server Driver]COUNT field incorrect[/color][/color][/color]
of[color=blue][color=green][color=darkred]
> > > syntax error. Where is the problem? I use questionmark because I need
> > > unnamed parameter veiw.
> > >
> > > select ProductID, NameOfProduct from dbo.Products where ProductID < ?
> > >
> > > Thank you in advance,
> > >
> > > Danilo Popoviæ, B. Sc.
> > > Trgostil d.d. - IC
> > > Toplièka 16
> > > 49240 Donja Stubica
> > > Croatia
> > >
> > >[/color]
> >
> > You can't use parameters with a view. If you have MSSQL2000, then you[/color]
> could[color=green]
> > use a table-valued function:
> >
> > create function dbo.GetProducts (@ProductID int)
> > returns table
> > as
> > return (select ProductID, NameOfProduct from dbo.Products where[/color][/color]
ProductID[color=blue]
> <[color=green]
> > @ProductID)
> >
> > select * from dbo.GetProducts (5)
> >
> > Otherwise, you can use a stored procedure.
> >
> > Simon
> >
> >[/color]
>
>[/color]