T-SQL Multi Use Function to Replace SQL in Procedures

Question

Let's assume I have the same following code in three procedures:

    SELECT CASE
           WHEN [column1] LIKE '%/[a-z][a-z]-[a-z][a-z]/%' THEN REVERSE ( SUBSTRING ( REVERSE ( [column1] ) ,1,PATINDEX ( '%/%',REVERSE ( [column1] )) - 1 )) 
           WHEN [column1] LIKE '' THEN ISNULL ( NULLIF ( [column1],'' ) ,'(not set)' ) 
           ELSE 'Other'
           END AS ColumnReturned
FROM X

1) Is it possible to have a "function" instead of this piece of code stored somewhere else, in one place? So then I can call only:

SELECT myFunction (column1)
FROM X

in the procedures. So if I want to change the logic, I'd change it only in one place.

2) If I could have such function as per 1) point, can I have also multiple functions in one "file"? So I have list of different functions in one place? Then I'd be able to call:

SELECT functionList.myFunction1 (column1)
      ,functionList.myFunction2 (column2)
FROM X

Or the functions need to be separeated and only share scheme?

I am new to T-SQL functions so will be happy if you could point me some starting point.

test the performance difference before committing to this change — Beth
– Beth, Commented Sep 17, 2015 at 14:54

GarethD · Accepted Answer · 2015-09-17 14:51:57Z

2

The basic syntax for what you want is:

CREATE FUNCTION dbo.YourFunction (@Column VARCHAR(50))
RETURNS VARCHAR(50)
AS
BEGIN
    RETURN  CASE
                WHEN @Column LIKE '%/[a-z][a-z]-[a-z][a-z]/%' 
                    THEN REVERSE ( SUBSTRING ( REVERSE ( @Column ) ,1,PATINDEX ( '%/%',REVERSE ( @Column )) - 1 )) 
                WHEN @Column LIKE '' 
                    THEN ISNULL ( NULLIF ( @Column,'' ) ,'(not set)' ) 
                    ELSE 'Other'
                END 
END

You would then just use:

SELECT dbo.YourFunction (x.column1) AS ColumnReturned
FROM X;

HOWEVER, scalar user defined functions can be a massive performance drain, and it may improve performance to convert it to an inline table valued function instead, so the function would be:

CREATE FUNCTION dbo.YourFunction (@Column VARCHAR(50))
RETURNS TABLE
AS
RETURN  
(   SELECT  CASE
                WHEN @Column LIKE '%/[a-z][a-z]-[a-z][a-z]/%' 
                    THEN REVERSE ( SUBSTRING ( REVERSE ( @Column ) ,1,PATINDEX ( '%/%',REVERSE ( @Column )) - 1 )) 
                WHEN @Column LIKE '' 
                    THEN ISNULL ( NULLIF ( @Column,'' ) ,'(not set)' ) 
                    ELSE 'Other'
            END AS ColumnReturned
);

This makes calling the function a bit more complicated, but it is often worth it:

SELECT (SELECT f.ColumnReturned FROM dbo.YourFunction (x.column1) AS f) AS ColumnReturned
FROM X;

OR

SELECT f.ColumnReturned
FROM X
    CROSS APPLY dbo.YourFunction (x.Column1) AS f;

edited Sep 17, 2015 at 14:51

answered Sep 17, 2015 at 13:49

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DNac Over a year ago

Thanks for your effort. Could you please also point me to some source where can I learn more about the "performance drain" you mentioned?

GarethD Over a year ago

Sorry, I thought I had put these in the answer. Here's a couple - How to Make Scalar UDFs Run Faster (SQL Spackle), SQL Server Pro - Inline Scalar Functions

Collectives™ on Stack Overflow

T-SQL Multi Use Function to Replace SQL in Procedures

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related