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
Ask ANY Question

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

Troubleshooting
Research
Professional Opinions
Ask a Question
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

troubleshooting Question

Trying to understand how CROSS APPLY works

Avatar of raymurphy
raymurphy asked on
Microsoft SQL Server 2005
4 Comments1 Solution354 ViewsLast Modified:
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]
(
      @ID INT,
      @Split VARCHAR(4000),
      @Length INT
)
RETURNS @Values TABLE
(
      [ID] INT,
      [RowID] INT,
      [Value] VARCHAR(500)
)
AS
BEGIN
      IF @Split IS NOT NULL
      BEGIN
            DECLARE @Index INT,
            @Loop INT

            SET @Index = 0
            SET @Loop = (SELECT CEILING(CONVERT(FLOAT, LEN(@Split)) / CONVERT(FLOAT, @Length)))

            WHILE @Index < @Loop
            BEGIN
                  INSERT INTO @Values VALUES(@ID, @Index + 1, SUBSTRING(@Split, (@Index * @Length) + 1, @Length))
                  SET @Index = @Index + 1
            END
      END
      RETURN
END

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.

Thanks.