We help IT Professionals succeed at work.
Get Started

SQL - Is it possible to embed function into SQL Select calling script

59 Views
Last Modified: 2020-03-10
Is it possible to embed a function in the calling SQL Select script?   For example; I have the DelimitedSplit8K function below and i want to know if it is possible to embed this function right into this  SQL Select calling script this way i have everything in one executable script.  I understand i would not be able to share the function.  

SELECT k_id.Item, k_qty.Item
FROM #data d
CROSS APPLY dbo.DelimitedSplit8K(d.[Kits Reference ID], ';') AS k_id                 ----> can i embed the function here somehow?
CROSS APPLY dbo.DelimitedSplit8K(d.[Kits Reference Qty], ';') AS k_qty
WHERE k_qty.ItemNumber = k_id.ItemNumber


Function:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[DelimitedSplit8K] (
    @pString varchar(8000),
    @pDelimiter char(1)
    )
RETURNS TABLE WITH SCHEMABINDING
AS
/*SELECT * FROM dbo.DelimitedSplit8K('ab/c/def/ghijklm/no/prq/////st/u//', '/')*/
RETURN
/*Inline CTE-driven "tally table" produces values from 0 up to 10,000: enough to cover varchar(8000).*/
WITH E1(N) AS (SELECT N FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS Ns(N)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    ctetally(N) AS (/* This provides the "zero base" and limits the number of rows right up front,
                       for both a performance gain and prevention of accidental "overruns". */
        SELECT 0 UNION ALL
        SELECT TOP (DATALENGTH(ISNULL(@pString, 1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    ), cteStart(N1) AS ( /* This returns N+1 (starting position of each "element" just once for each delimiter). */
        SELECT t.N+1
        FROM ctetally t
        WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
    )
/* Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. */
SELECT ROW_NUMBER() OVER(ORDER BY s.N1) AS ItemNumber,
       SUBSTRING(@pString, s.N1, ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1), 0) - s.N1, 8000)) AS Item
FROM cteStart s;
/*end of func*/
GO
Comment
Watch Question
CERTIFIED EXPERT
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 1 Answer and 1 Comment.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE