SQL Server : create view inside CASE

Question

I am trying to create views inside a case statement, but SQL Server does not allow it. Here an example:

SELECT value,
CASE value
  WHEN 'value1' THEN (Create View as (Select ... From ... Where...))
  WHEN 'value1' THEN (Create View as (Select ... From ... Where...))
END
FROM table
WHERE table.parameter = 'some_parameter'

Does anybody know why it doesn't work? Thanks in advance.

Maybe because `CASE` in T-SQL is an **expression** - and not a statement .... — marc_s, Nov 09 '15 at 14:55
That approach is not possible - creating data structures (OK, views) in a SELECT statement (the purpose of which is to get data) is a **very** strange idea ... why would that be needed? (I am asking, because it does not really look like a good idea in any case that comes to my mind) — Dimitar Kyuchukov, Nov 09 '15 at 17:06
I have a program, that depending on the parameter from tableA (this parameter is a column stored as string in a table), should create a view with this column. Depending on the parameter, different views should be created — bpesunny, Nov 10 '15 at 07:46

score 2 · Answer 1 · answered Nov 09 '15 at 15:29

You are mixing DDL and DML here.

CREATE VIEW is DDL (data definition language) and cares about data structures (tables, views, procedures, triggers, ...)

A SELECT statement on the other hand is DML (data management language) and cares about data. You can select, update, insert or delete data. Some people even consider SELECT not even to be part of DML, because it doesn't change data.

You cannot use SELECT to invoke DDL. SELECT is supposed to get some data from your tables, not to create a view or table.

What do you actually want to achieve, anyway? Why would you ever want to change your data structures based on mere data? Data structures should be fix and only data itself should be added, changed or deleted.

(Yes, from time to time you may want to adjust data structures, e.g. add an index, because you notice that data access in an app is too slow, or even change a table and add a new column. And you may want to add a view some time, because you are looking for a more convenient way to select data in a new app, but all these things are rare.)

The only case I can think of right now, when you would have a table that shall lead to DDL would be this: You are a DBA and deleveloped an app where the developers enter their DDL requests. Then you have an app running over the authorized entries and performin the appropriate DDL. You would do this either outside the DBMS (an app in C#, Java or whatever selecting data from the database, building the DDL statements and having them executed) or inside the DBMS in a stored procedure building dynamic DDL statements.

I have a program, that depending on the parameter from tableA (this parameter is a column stored as string in a table), should create a view with this column. Depending on the parameter, different views should be created — bpesunny, Nov 10 '15 at 07:46
Then do as I say, retrieve the parameter, build the create view statement and have it run. I am just saying that it is very, very rare to create views from a *program*. This is an action so infrequently needed that you'd usually just do it manually then. — Thorsten Kettner, Nov 10 '15 at 07:56

Olivier De Meulder · Answer 2 · 2015-11-09T17:01:13.273

0

In t-sql, which is the SQL flavor of MS SQL Server, the case statement is meant to return one value, based on certain conditions.

If you want to introduce branching logic in t-sql, you can do that use if and else.

Here is an example, based on your example:

declare @Value varchar(128);
select top 1 @Value = value from table where table.parameter = 'some_parameter';

if @Value = 'value1'
begin
    Create View as (Select ... From ... Where...);
end
else if @Value = 'value2'
begin
    Create View as (Select ... From ... Where...);
end

Note: the begin and end blocks is optional, if you only have one statement within it.

edited Nov 09 '15 at 17:01

answered Nov 09 '15 at 14:25

Olivier De Meulder

2,493
3
25
30

That will only work if there is only a single record matching the 'where' clause, which is not necessarily the case (so you might want to add TOP 1 when assigning @Value) ... in fact a loop through all the matches of the 'where' condition might be needed. – Dimitar Kyuchukov Nov 09 '15 at 17:00

SQL Server : create view inside CASE

2 Answers2