IF condition in view in SQL Server

问题:

Is it possible to have a if condition in VIEWS

eg

CREATE VIEW
as

  DECLARE @Count int
  SET @Count=-1
  select @Count=EmpID from EmployeeDetails where ID=200
  IF @Count=-1
  BEGIN
    SELECT * FROM TEAM1
  END
  ELSE
  BEGIN
    SELECT * FROM TEAM1
  END

回答1:

No I don't believe this is possible.

You could use a stored procedure instead to achieve this functionality.

回答2:

You could try something sneaky with a UNION :

SELECT {fieldlist}
FROM Table1
WHERE EXISTS(SELECT EmpID FROM EmployeeDetails WHERE ID = 200)

UNION ALL

SELECT {fieldlist}
FROM Table2
WHERE NOT EXISTS(SELECT EmpID FROM EmployeeDetails WHERE ID = 200)

This method would require both SELECT statements to return the same set of fields, although their sources might be different.

回答3:

Views only allow select statements as stated in here

if you need to do if on column values you can use a

SELECT
CASE WHEN COLUMN1 = 1 THEN COLUMNX ELSE COLUMNY END
FROM TABLE1

if your need exceeds this you should create a select from a table valued function instead of a view.

What you need is a simple Procedure

CREATE PROCEDURE DOSOMETHING
(   
    @ID INT
)
AS
BEGIN
    IF @ID > 100
        SELECT 1 AS ID,'ME' AS NAME, GETDATE() AS VARIABLEDATECOL, NEWID() AS VARIABLEGUID
    ELSE
        SELECT 2 AS ID, 'YOU' AS NAME
END

回答4:

simply use a udf (User defined Function) Here you can use IF, ELSE, WHILE etc.

But when you are manipulating data (INSERT, UPDATE, DELETE) then you have to use Stored Procedures because udf's aren't able to do that