CODEWITHSUNDEEP
sqlsql-servert-sqluser-defined-functions
TheGeneral
TheGeneral

Reputation: 81573

How to create a User Defined Function for SQL server

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.

Upvotes: 1

Views: 692

Answers (1)

Jaugar Chang
Jaugar Chang

Reputation: 3196

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
 ...

Upvotes: 1

Related Questions