Eric Wise

Sponsors

The Lounge

Wicked Cool Jobs

Syndication

Recent Posts

Tags

View more

Blogs I Read

Fun & Games

Archives

Advertisement

Eric Wise » Tricky SQL Stored Procedure *EDIT*
Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
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

Go

Posted Mon, Aug 16 2004 12:49 PM by Eric Wise

[Advertisement]

Comments

Mark Bonafe wrote re: Tricky SQL Stored Procedure
on Mon, Aug 16 2004 9:42 AM
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!
Mark Bonafe wrote re: Tricky SQL Stored Procedure
on Mon, Aug 16 2004 9:53 AM
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.
Eric Wise wrote RE: Tricky SQL Stored Procedure
on Mon, Aug 16 2004 10:35 AM
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.
Mark Bonafe wrote re: Tricky SQL Stored Procedure
on Mon, Aug 16 2004 12:08 PM
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! ;)
Devlicio.us