How to create a User Defined Function for SQL server

Question

I have a fairly large stored procedure that I'm trying to simplify for readability.

It contains many union clauses with statements like the one shown below:

Fig.1

SELECT COUNT(1) AS Total
  FROM Orders 
  WHERE (NOT EXISTS (
      SELECT 1 
        FROM (
          SELECT Id 
            FROM OrderLineItems 
            WHERE Orders.Id = Order_Id) AS Sub
        WHERE EXISTS (
            SELECT 1 
              FROM NormalizedLineItems 
              WHERE (Sub.Id = OrderLineItem_Id)
                AND (OutOfStock = 1))))
    AND (EXISTS (
      SELECT 1 AS Total
        FROM OrderShipments 
        WHERE (Orders.Id = Order_Id)
          AND (CarrierApproved = 0)))
    AND (IsQuote = 0)
    AND (Cancelled = 0)
    AND (Archived = 0)
    AND (Completed = 0)
    AND (Holding = 0)

However there are many reoccurring patterns in each statement.

The following pattern turns up several times Fig.2

Fig.2

  WHERE (NOT EXISTS (
      SELECT 1 
        FROM (
          SELECT Id 
            FROM OrderLineItems 
            WHERE Orders.Id = Order_Id) AS Sub
        WHERE EXISTS (
            SELECT 1 
              FROM NormalizedLineItems 
              WHERE (Sub.Id = OrderLineItem_Id)
                AND (OutOfStock = 1))))

I'm trying to (for readability purposes) reduce the code in the master stored procedure

So i thought id performance test a UDF, I've come up with the following Fig.3

Fig.3

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION TestFunction (@OrderId int)
RETURNS TABLE
AS
  RETURN
  (
  -- Add the SELECT statement with parameter references here
  SELECT 1 AS Total
    FROM (
      SELECT OrderLineItems.Id AS Id
        FROM OrderLineItems
        WHERE @OrderId = Order_Id) AS Sub
    WHERE EXISTS (
        SELECT 1 AS Total
          FROM NormalizedLineItems
          WHERE (Sub.Id = OrderLineItem_Id)
            AND (OutOfStock = 1)))
GO

All though the above compiles, I'm not really sure I'm on the right track, I'm having all sorts of problems trying to apply the above UDF to the original query.

I am seeking a concrete example of how to abstract Fig.2 from Fig.1 into a UDF so I can at least performance-test the solution to see if it's worthwhile.

Note: I do know user defined functions can be a performance nightmare, however I'm not even at a stage where I can test.

It might be the last choice using UDF to simplify your code in where clause. Use CTE or regular table for reusable data is a good practice. Or EXEC dynamic querys combinated by different pieces of sql statement strings. — Jaugar Chang
– Jaugar Chang, Commented Sep 6, 2014 at 2:51
Thanks for your comments and answer, ill research the points you made, and test your code, gives me food for thought — TheGeneral
– TheGeneral, Commented Sep 6, 2014 at 2:55

Jaugar Chang · Accepted Answer · 2014-09-06 03:03:34Z

1

Create an order_ids table with only one column named order_id

Insert into order_ids
select order_id from 
  FROM Orders 
  WHERE (NOT EXISTS (
      SELECT 1 
        FROM (
          SELECT Id 
            FROM OrderLineItems 
            WHERE Orders.Id = Order_Id) AS Sub
        WHERE EXISTS (
            SELECT 1 
              FROM NormalizedLineItems 
              WHERE (Sub.Id = OrderLineItem_Id)
                AND (OutOfStock = 1))))

Then you can simplify your Sql like this:

SELECT COUNT(1) AS Total
  FROM Orders 
  join order_ids
  on order_ids.order_id = Orders.order_id
 ...

If your reoccurring statement only in one query, Common Table Expression is the best choice:

with CTE_order_ids as
(select order_id from 
  FROM Orders 
  WHERE (NOT EXISTS (
      SELECT 1 
        FROM (
          SELECT Id 
            FROM OrderLineItems 
            WHERE Orders.Id = Order_Id) AS Sub
        WHERE EXISTS (
            SELECT 1 
              FROM NormalizedLineItems 
              WHERE (Sub.Id = OrderLineItem_Id)
                AND (OutOfStock = 1))))
)
SELECT COUNT(1) AS Total
  FROM Orders 
  join CTE_order_ids 
  on order_ids.order_id = Orders.order_id
 ...

edited Sep 6, 2014 at 3:03

answered Sep 6, 2014 at 2:36

Jaugar Chang

3,2062 gold badges15 silver badges24 bronze badges

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

1 Comment

TheGeneral Over a year ago

Thanks for this, ive tested it out, and it also gives me a good performance benifit

Collectives™ on Stack Overflow

How to create a User Defined Function for SQL server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related