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)
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 )
SELECT * FROM view1
-- how to add the if here for view 2?
--if @id=10 then view1 else view2
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;