CODEWITHSUNDEEP
sqlsql-serverstored-proceduresviewuser-defined-functions
Aleksandar
Aleksandar

Reputation: 1173

Create View based on condition in SQL Server

This question is asked several times, but I cann't figure out some things.

First, I have situation that I need to create view (must be view), but according to the result of one query.

create view as
if (select count(oid)...)>1
   select1
else
   select2

I know that this form is not allowed. But how can I accomplish something like this? I have tens of views like this I need to create. Because these SELECT statements contain spatial conditions, and the database is big and has many many tables, I need the 'most optimal' solution, or it will query forever.

I read something about using stored procedures and UD functions, but how to create view from them and get max performances? I don't need parameters in it, just way to create views defined by this condition.

Upvotes: 0

Views: 11775

Answers (3)

Laurence
Laurence

Reputation: 10976

If you just need to create a view from a stored procedure (rather than actually returning the data form the view) you need to use dynamic SQL:

create proc dbo.makeview as
if (select count(old) > 1 from somthing)
begin
    exec sp_executesql N'create view dbo.view1 as select x from y';
end
else
begin
    exec sp_executesql N'create view dbo.view1 as select a from b';
end;

Of course, doing it this way, the views don't change if the conditions change. You might be able to manage things such that you recreate the views if the conditions change some other way.

To do it more dynamically, and if select1 and select2 have the same output columns, you could use something like

create view dbo.view1 as
with x as (
    select
        case when count(old) > 0 then 0 else 1 end cond
    from
        something
), y as (
    select
        0 as disc,
        rest_of_select1
    union all
    select 
        1,
        rest_of_select2
) select
    col1,
    col2
from
    y 
        inner join
    x
        on x.cond = y.disc
option
    (recompile);

I doubt it will perform particularly well, though

Example SQLFiddle

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294407

The 'most optimal solution' is the IF statement. Anything else you attempt to do will be, arguably, sub-optimal. Very likely extremly suboptimal. So this rules out a view.

The problem with queries that tries to express your conditional 2 selects (which is possible to do, but is a bad idea) is that a query needs to be compiled into a specific execution plan. There are no 'conditional' execution plans, the plan must work for all cases. Even though likely select1 and select2 would each have an optimal execution plan, the query that expresses both of them in a single SELECT will not have any optimal plan. The query will probably end up with a brute force plan that scan all the table, ignoring all indexes.

Even trying to cheat with a view based on a multi-statement table value function is unlikely to work (at least not efficiently). Just don't do it. go back to your design board and try to design something that uses your database, not fights against it.

Upvotes: 0

Jeffrey Wieder
Jeffrey Wieder

Reputation: 2374

You can have a view call a stored procedure.

create view ConditionalView as
execute sp_ConditionalView

Then put you conditional code in the stored procedure.

Upvotes: -1

Related Questions