Skip to content

Instantly share code, notes, and snippets.

@sinairv
Created September 17, 2012 06:54
Show Gist options
  • Save sinairv/3735919 to your computer and use it in GitHub Desktop.
Save sinairv/3735919 to your computer and use it in GitHub Desktop.
Template for creating and testing SQL function and view pairs
-- 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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment