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