1

I have a small bit of code that I need to execute over and over again. I'm splitting a string into first, middle, and last names.

DECLARE @SpaceIndex int,
    @LastSpaceIndex int,
    @Length int

SET @Length = LEN(@Name)
SET @LastSpaceIndex = @Length - CHARINDEX(' ', REVERSE(@Name))
Set @SpaceIndex = CHARINDEX(' ', @Name)

    SELECT SUBSTRING(@Name, 0, @SpaceIndex) As FirstName, 
        SUBSTRING(@Name, @SpaceIndex + 1, @LastSpaceIndex - @SpaceIndex) As MiddleName, 
        SUBSTRING(@Name, @LastSpaceIndex + 1, @Length - @LastSpaceIndex) As LastName

I've decided to turn this into a table function or sproc with output parameters, but I'm not sure which way is better.

What is the best way to return three values in SQL?

For reference names can look like this: 'John Doe', 'Doe', 'John W Doe', 'Jane Whilma Alice Doe'

Thanks

Chloe
  • 483
  • 4
  • 14
  • Not sure about "better", but I usually prefer a function over a stored procedure just so I can use `APPLY`. – Bacon Bits Oct 27 '14 at 20:43

1 Answers1

1

The most efficient way would be to handle this in the client software. Assuming that you cannot, it seems that output parameters are the more efficient method of the two.

Sources:

Community
  • 1
  • 1
Scott T
  • 283
  • 1
  • 11