piris piris - 3 months ago 10
SQL Question

SQL Views (If Statement)

I have two different views which have the same exact columns, but different data. They are called depending on a value of a key.

Now, instead of having my sql functions with an if statement, can i do that somewhere centralized. Like a new view with an IF statement? (I know this is not possible)

UPDATE

Based on the comments below, i need to create a sql table function, can you help me with the code below?

CREATE FUNCTION GetCorrectData ( @id INT )
RETURNS TABLE
AS
RETURN
(

SELECT * FROM view1
-- how to add the if here for view 2?
--if @id=10 then view1 else view2
)


Thanks

Answer

You might look at this:

Both views return two columns of string-type.

This inline/ad-hoc approach should be faster as procedural approaches with IF. The inline function is predictable, the optimizier will be able to use indexes and statistics.

CREATE VIEW dbo.Test1 AS
SELECT COLUMN_NAME,TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS;
GO

CREATE VIEW dbo.Test2 AS
SELECT TABLE_SCHEMA,TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE';
GO

CREATE FUNCTION dbo.MyFunc(@key INT)
RETURNS TABLE
AS
RETURN
SELECT * FROM dbo.Test1 WHERE @Key=1
UNION ALL
SELECT * FROM dbo.Test2 WHERE @Key=2;
GO

SELECT * FROM dbo.MyFunc(1); --try 2 as well
GO

DROP FUNCTION dbo.MyFunc;
DROP VIEW dbo.Test2;
DROP VIEW dbo.Test1;
Comments