Eric Wise

Business & .NET

Tricky SQL Stored Procedure EDIT

*Edited thanks to Mark's insight*

Figured I'd post this sql stored procedure that's been giving me a migraine for the last 5-6 hours.

Basically I'm querying a webservice that returns a big message, and I wanted to store it in another company's POS system to print out on the receipt at the counter. Problem is the receipt is only 44 characters wide and they store receipt messages in a TransactionID, LineNumber, Message format in the table so I had to parse it myself while being sure not to break up any words.

It's pretty easy to modify it to specify any number of characters per line. Here's the result:

CREATE PROCEDURE dbo.ParseReceiptMessage(
@TransactionID int,
@Message nvarchar(4000)
)
AS

DECLARE
@CursorPos int,
@LastSpacePos int,
@TextLength int,
@PrintOrder int,
@LineMessage nvarchar(44)

SET @TextLength = len(@Message)
SET @PrintOrder = 1
SET @CursorPos = 0
SET @LastSpacePos = 1

WHILE ( @CursorPos < @TextLength)
BEGIN
SET @CursorPos = @CursorPos + 44

      SET @LineMessage = SUBSTRING(@Message, @CursorPos - 43, @CursorPos)
      SET @LastSpacePos = (@CursorPos - (PATINDEX('% %', REVERSE(@LineMessage))-1)) - (@CursorPos - 44)
      SET @LineMessage = SUBSTRING(@Message, @CursorPos - 43, @LastSpacePos)

      INSERT INTO SomeTable
         (TransactionID, PrintOrder, Message)
      VALUES
         (@TransactionID, @PrintOrder, @LineMessage)

      SET @CursorPos = @CursorPos - (44 - @LastSpacePos)
      SET @PrintOrder = @PrintOrder + 1
END

Published Aug 16 2004, 12:49 PM by Eric Wise

Comments

Mark Bonafe said:

Hey Eric - That *is* a tricky little procedure, indeed. Please don't think I'm being picky, but I think I found a minor flaw. I have a client with *majorly* twisted reporting logic. So I have seen my share of tricky SQL. This solution interested me because I have a similar situation. I like your solution to finding the last space position using PATINDEX and REVERSE. It's a lot easier than what I normally do. Anyway - to the bug...

I ran the procedure replacing the INSERT statements with "SELECT @TransactionID, @PrintOrder, @LineMessage"

I used the following text from your blog as the message:
DECLARE
@TransactionID int,
@Message nvarchar(4000)
SET @TransactionID = 1
SET @Message = 'Basically I am querying a webservice that returns a big message, and I wanted to store it in another company POS system to print out on the receipt at the counter. Problem is the receipt is only 44 characters wide and they store receipt messages in a TransactionID, LineNumber, Message format in the table so I had to parse it myself while being sure not to break up any words'

The problem is with the last line. It trimmed 'break up any words' to 'ak up any words' The problem is with the constant at the beginning of the loop, 44. The LastSpacePos is 40, that is what should be used, but only on the last line. Try replacing the first IF block with this:

IF @CursorPos > @TextLength
BEGIN
-- Recalculate the @CursorPos
-- Replace the 44 characters with the LastSpacePos(ition)
-- (plus one because the LastSpacePos was - well - a space!
SET @CursorPos = @CursorPos - 44 + @LastSpacePos + 1
SET @LineMessage = SUBSTRING(@Message, (@CursorPos - @LastSpacePos), @TextLength)
..... rest of procedure.....

I think that will work better for you. You might want to set an int to 44 at the top of the procedure and then use that instead of hard coding. Then changing the length would truly be a piece-o-cake!

# August 16, 2004 9:42 AM

Mark Bonafe said:

Actually Eric, I don't think you need the IF block at all. The line:

SET @LineMessage = SUBSTRING(@Message, @CursorPos - 43, @CursorPos)

in the ELSE section should work everytime! You are already subtracting 43 from your constant of 44, which covers for the space. I've tried running the procedure using only the the code in the ELSE with multiple length strings and I cannot break it. It always returns the correct result.

# August 16, 2004 9:53 AM

Eric Wise said:

You rock Mark, I'll update the parent post tomorrow!

SQL is definately something I've been trying to become more advanced at. Though I wonder about the usefulness once 2005 comes and I can just throw a c# script in there.

# August 16, 2004 10:35 AM

Mark Bonafe said:

Your welcome! About the C# in SQL Server, I still don't know how I feel about it. You can write sloppy C# just as easy as any other language. Besides, the namespace will have to offer up just as many weird twists as T-SQL. I guess we'll have to wait until 2006 - uh - 2005 to find out! ;)

# August 16, 2004 12:08 PM

Name (required)

Your URL (optional)

Comments (required)

Remember Me?