StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PONo idea how to handle an error while calling substring on a sql variable
text
Body
copied!<p>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.</p> <p>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,'</p> <p>I'm just crap with SQL Server and have no idea how to gracefully handle those situations. Could someone provide some advice for me?</p> <p>Here is my stored procedure:</p> <pre><code>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 </code></pre> <h3>Edit with sample data and expected results</h3> <p>Here's what the positions table looks like:</p> <pre><code>PositionId, PositionName 1, Defensive End 2, Quarterback 3, Pitcher 4, Catcher 5, First Base </code></pre> <p>The positions are saved in a single table row like this:</p> <pre><code>1,2,3, 1,0,0, 0,0,0, </code></pre> <p>The output would look like this:</p> <blockquote> <pre><code>Defensive End, Quarterback, Pitcher Defensive End No position selected </code></pre> </blockquote> <p>"No position selected" is what I'd like to have output in the instance of three zeros.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload