No idea how to handle an error while calling substring on a sql variable

I'm trying to figure out how to handle an error that gets thrown at the final set statement, wherein I call substring on the current @return value and set the result the same return value.

The stored procedure takes in a string of numbers like this: '135, 34, 21,'. The trailing comma is an artifact of a poorly designed system. This procedure runs the string through a split function to create a temp table, and then does a look up on each of the items in the string, returning a name associated with each of the three values. The error is thrown when all three values of the parameter are 0: '0,0,0,'

I'm just crap with SQL Server and have no idea how to gracefully handle those situations. Could someone provide some advice for me?

Here is my stored procedure:

declare @positions table
    (orderID int identity(1,1), positionID int)
declare @counter int
declare @positionMax int
declare @currentPositionID int
declare @return varchar(max)

set @counter = 1
set @return = ''

insert @positions
select items
from dbo.Split(
    (select Positions
     from dbo.athletes
     where athleteID = 3701, ',')

select @positionMax = max(orderID) 
from @positions

while(@counter <= @positionMax)
begin
    select @currentPositionID = tp.PositionID
    from db.positions tp
        inner join @positions p
            on tp.PositionID = p.positionID
    where p.orderID = @counter

    select @return = @return + PositionName + ', '
    from dbo.positions
    where PositionID = @currentPositionID

    set @counter = @counter + 1
end

set @return = substring(@return, 1, (len(@return) - 1))

select @return

Edit with sample data and expected results

Here's what the positions table looks like:

PositionId, PositionName
1, Defensive End
2, Quarterback
3, Pitcher
4, Catcher
5, First Base

The positions are saved in a single table row like this:

1,2,3,
1,0,0,
0,0,0,

The output would look like this:

Defensive End, Quarterback, Pitcher
Defensive End
No position selected

"No position selected" is what I'd like to have output in the instance of three zeros.

Solution 1:

I'm guessing your error is caused because @return is 0 characters long. A simple option could be to add the value 0, 'No position selected' to the positions table.

A nicer option could be to add an IF statement around the set statement:

if len(@return) > 0
BEGIN
    set @return = substring(@return, 1, (len(@return) - 1))
END
ELSE
BEGIN
    set @return = 'No position selected'
END

You could also add a Try/Catch block if using SQL to catch any generic exceptions. See msdn try/catch

Also, not sure why you're doing the while loop. There are generally better options in SQL like using a CTE.

Solution 2:

Here's one way to do it without the split function and without the loop. Note that this doesn't turn out in the exact order of your expected output.

DECLARE @pos TABLE
(
    PositionId   INT,
    PositionName VARCHAR(32)
);

INSERT @pos SELECT 1, 'Defensive End'
UNION ALL   SELECT 2, 'Quarterback'
UNION ALL   SELECT 3, 'Pitcher'
UNION ALL   SELECT 4, 'Catcher'
UNION ALL   SELECT 5, 'First Base';

DECLARE @row TABLE(pList VARCHAR(32));

INSERT @row SELECT '1,2,3,'
UNION ALL   SELECT '1,0,0,'
UNION ALL   SELECT '0,0,0,';

;WITH cte AS
(
    SELECT x.pList, p.PositionName 
    FROM @row AS x LEFT OUTER JOIN @pos AS p 
    ON ',' + x.pList LIKE '%,' + CONVERT(VARCHAR(12), p.PositionID) + ',%'
)
SELECT COALESCE(NULLIF(STUFF((
    SELECT ', ' + PositionName 
      FROM cte AS cte2 WHERE cte.pList = cte2.pList
      FOR XML PATH(''), TYPE).value(N'./text()[1]',N'varchar(max)'), 1, 2, ''), ''), 
      'No position selected'
  )
  FROM cte
  GROUP BY pList;

EDIT

Here is a slightly different version that seems to obey the order in the original list and relies on a numbers table...

CREATE TABLE dbo.Numbers(n INT PRIMARY KEY);

INSERT dbo.Numbers(n) SELECT TOP 1000 ROW_NUMBER() OVER
 (ORDER BY s1.[object_id]) FROM sys.all_objects AS s1
 CROSS JOIN sys.all_objects AS s2;

...and the OPTION (FORCE ORDER) hint:

;WITH cte AS 
(
    SELECT *, r = ROW_NUMBER() OVER (PARTITION BY pList ORDER BY n.n) 
        FROM @row AS r LEFT OUTER JOIN @pos AS p
    ON ',' + r.pList + ',' LIKE '%,' + RTRIM(p.PositionId) + ',%'
    LEFT OUTER JOIN dbo.Numbers AS n
    ON n.n = CHARINDEX(',' + RTRIM(p.PositionId) + ',', ',' + r.pList + ',')
)
SELECT pList, Position = COALESCE(STUFF((SELECT ',' + PositionName 
    FROM cte AS cte2 INNER JOIN dbo.Numbers AS n ON n.n = cte2.r
    WHERE cte2.pList = cte.pList FOR XML PATH(''), TYPE
    ).value('./text()[1]', N'nvarchar(max)'), 1, 1, ''), 'No position selected')
FROM cte GROUP BY pList OPTION (FORCE ORDER);

This relies on the assumption that the optimizer will choose the clustered primary key on the numbers table. To enforce this further it may make sense to ensure that that index is used by naming it using the WITH (INDEX) hint on both of the joins to dbo.Numbers (which means you should probably name the primary key constraint vs. doing it the lazy way like I did above).

Sorry it took me a bit to come back to this.

Solution 3:

Please try there

--------------sample table ---------------------------------------

create table #tmp_table (PositionId int, PositionName varchar(50))

insert into #tmp_table values (1, 'Defensive End')
insert into #tmp_table values (2, 'Quarterback')
insert into #tmp_table values (3, 'Pitcher')
insert into #tmp_table values (4, 'Catcher')
insert into #tmp_table values (5, 'First Base')

create table #postition (post varchar(20))
insert into #postition values ('1,2,3,')
insert into #postition values ('1,0,0,')
insert into #postition values ('0,0,0,')

----------------------------------stored procedure -------------------------------

-- temp result table
create table #tmp_result (post_list varchar(2000))

DECLARE post_cursor CURSOR 
    STATIC READ_ONLY
    FOR 
    select post     
    from #postition

-- temp variable
declare @post varchar(20)

OPEN post_cursor

FETCH NEXT FROM post_cursor INTO @post

-- remove last comma
SET @post = LEFT(@post,LEN(@post) - 1)

WHILE @@FETCH_STATUS <> -1
BEGIN
    -- do stuff
    exec (
            '
            insert into #tmp_result(post_list)
            SELECT STUFF(
            (
                SELECT '','' + PositionName
                FROM #tmp_table C
                WHERE positionId in (' + @post + ')
                FOR XML PATH('''')
            ), 1, 1, '''') 
        ')
    -- fetch again
    FETCH NEXT FROM post_cursor INTO @post

    SET @post = LEFT(@post,LEN(@post) - 1)  

END

CLOSE post_cursor
DEALLOCATE post_cursor


-- return result
select *
from #tmp_result