Dynamic views

Is it possible to use an IF statement or CASE when creating a view?
I want to something like.
IF Myvariable=n
selelect * from mytable where X=n
Else
selelect * from mytable where X=aYou can use CASE in a view but not IF. You cannot use variables or
parameters in views though. Do this in the WHERE clause when you query
the view.
BTW, don't use SELECT * in views. The results can be unreliable if the
base table changes. List all the required columns by name.
David Portas
SQL Server MVP
--|||no. but you can use stored procedure instead
--
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Geo" wrote:

> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>
>|||Thanks guys, can I call a SPROC from within a view?
"Geo" <noSpamgbarr@.ibigroup.com> wrote in message
news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>|||Nope. If you need a 'parametrized view' you can achieve that by creating a
table function.
What exactly is the purpose of this view?
ML|||> Thanks guys, can I call a SPROC from within a view?
No. A view is no more than a select statement, you can not use variables,
parameters, dml statements other than "select", etc.
Can you tell us what are you trying to accomplish?
AMB
"Geo" wrote:

> Thanks guys, can I call a SPROC from within a view?
> "Geo" <noSpamgbarr@.ibigroup.com> wrote in message
> news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
>
>|||No. You might try using a table-valued function. Table-valued functions
can't call procs either but they do work quite like views and they can
contain procedural code and make use of parameters. See the CREATE
FUNCTION topic in Books Online.
David Portas
SQL Server MVP
--|||Try out UDF returning a TABLE.
UDF will let you pass parameters & can then be used in the FROM clause of a
SELECT statement same way you use tables & views.
Rakesh
"Geo" wrote:

> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>
>|||You are missing the concept of a VIEW. It is a virtual table, not a
procedure. Do you expect other tables to change on the fly? And CASE
is an expression, not a statement.|||hi geo,
its the other way around
A stored procedure can call a view
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Geo" wrote:

> Thanks guys, can I call a SPROC from within a view?
> "Geo" <noSpamgbarr@.ibigroup.com> wrote in message
> news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
>
>