Asked to support a new application in SQL 2005, and came across some code which works fine - but was just wondering if anyone could give a detailed breakdown of how this code works.
Specific functionality is a function which will break down a long single-line description (of up to 4000 chars) into multiple smaller chunks of VARCHAR(40).
Here is the function :
CREATE FUNCTION [dbo].[SplitColumnLength]
RETURNS @Values TABLE
IF @Split IS NOT NULL
DECLARE @Index INT,
SET @Index = 0
SET @Loop = (SELECT CEILING(CONVERT(FLOAT, LEN(@Split)) / CONVERT(FLOAT, @Length)))
WHILE @Index < @Loop
INSERT INTO @Values VALUES(@ID, @Index + 1, SUBSTRING(@Split, (@Index * @Length) + 1, @Length))
SET @Index = @Index + 1
and the application code joins to this function in SQL Server 2005 using CROSS APPLY - e.g. to split customer description into VARCHAR(40) chunks, it uses the following :
-- SELECT s.RowID, s.Value
-- FROM CustomerDetails b
-- CROSS APPLY dbo.SplitColumnLength(b.CustomerNo, b.CustomerDescription, 40) s
As I say, the code works fine - but to understand it, I was hoping someone could give a breakdwon of how the function itself works, and then how the associated CROSS APPLY use of that function (as per the above example) also works.