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

no. but you can use stored procedure instead
Show quoteHide quote

Thanks guys, can I call a SPROC from within a view?

Show quoteHide quote

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

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

Show quoteHide quote

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.

hi geo,

its the other way around

A stored procedure can call a view


Show quoteHide quote

you can try to improve this..

----------------------------------------------
use northwind
create  proc dynaselect
@option int
as
if @option=1
begin
    select * from employees
end
if @option=2
begin
    select * from categories
end
if @option=3
begin
    select * from [Invoices] --this is a view
end
go
exec dynaselect 1
exec dynaselect 2
exec dynaselect 3
----------------------------------------------------------------------


Show quoteHide quote

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

Show quoteHide quote

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.

Thanks for the input guys, I found a simpler way to achieve what I was
after. It's amazing what you can achieve when you look at a problem after a
weekend away from work. Basically I found a way to filter my result without
the use of an IF statement.

Thanks again for the help.

Geo



Show quoteHide quote

How Do I Avoid Update Deadlock
ordered insert - select is not inserting in order
Delete Without Writing To Transaction Log
clustered index on a identity field
ROWS with null values getting truncated.