Template for creating and testing SQL function and view pairs

SqlFunctionViewTemplate.sql

-- Template for creating and testing SQL function and view pairs

IF EXISTS 
    (SELECT * FROM sys.objects 
        WHERE object_id = OBJECT_ID(N'ufnSomeFunction') 
        AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ufnSomeFunction]
GO

CREATE FUNCTION [dbo].[ufnSomeFunction]
(
    @Var1 VARCHAR(50), @Var2 VARCHAR(50)
)
RETURNS TABLE

RETURN

SELECT * FROM SomeTable

GO


IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'vSomeFunctionTemp')
DROP VIEW [dbo].[vSomeFunctionTemp]

GO

CREATE VIEW [dbo].[vSomeFunctionTemp] AS
SELECT * FROM [dbo].[ufnSomeFunction]('Param1', 'Param2')

GO

-- You may need to split these assignments in SQL Server < 2012
DECLARE @Var1 VARCHAR(50) = 'P1';
DECLARE @Var2 VARCHAR(50) = 'P2';

SELECT * FROM [dbo].[ufnSomeFunction](@Var1, @Var2)