Stored Procedures Controlling Printing


The first attached stored procedure places asterisks on a Crystal Web Viewer report called the Bill of Lading.  These asterisks display, when the report is printed.

The second stored procedure controls the reprint of this bill of lading.

The final attached document is programming for a table called EPBLRPT.

The asterisks are not supposed to print if you reprint the report.  It seems to me that this is probably based on the data in the EPBLRPT report.  But, the only record in this table is one that is dated 03/09/2015.

Can someone please examine this programming and determine if there is a way to prevent the asterisks from displaying , when the report is printed more than once?

Thank you!  Much appreciated!

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Will you know it is a second print?
If not then I can't think of a way to do it.

TBSupportAuthor Commented:
Hi mlmcc:

No, I don't know if it's a second print.  But, since one of the stored procedures has the title "reprint" in it, I would think that there's a way to do it.

My thought was you could add a parameter to the SP that does the printing and test it before you add the ***** to the field.  Add ***** on first print and '     ' on subsequent prints.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

TBSupportAuthor Commented:
Hi mlmcc:

What would be the syntax, for this parameter?

You're talking about '*****'+orders.SOPNUMBE , correct?  Just making sure.

 The logic there has no obvious connection to reprinting.  It's just looking at orders.REQSHIPDATE.  The only connection would be if REQSHIPDATE was set to 1/1/1900 when (or after) the order was printed.  If that's the intent, then I guess the question would be, why isn't REQSHIPDATE being changed?  That might be the first thing to look at.

 It makes sense that EPBLRPT only contains one record, since the reprint SP can only use one value from that table.  I would assume that when you request a reprint, you ask for a specific date, and the program/whatever is supposed to put that date in EPBLRPT.  The 03/09/2015 date that you mentioned would presumably be the last date that someone asked to reprint.

 Adding a "reprint" parameter to sp_EP_BillOfLading might be an option, but anything that uses sp_EP_BillOfLading might need to be changed, to accommodate the new parameter.

 Another option would be to change how you use the @shopOrder parameter.  The advantage to that is that it won't affect anything else that uses sp_EP_BillOfLading, because the parameters won't change.  The reprint SP sets @shopOrder to null, which tells the main SP to include every SOPNUMBE (the main SP replaces the null with '%', and uses that in a Like comparison).  Since the reprint SP is not using @shopOrder to send specific values to the main SP, we can use it for something else.

 You could change the reprint SP to set @shopOrder to 'reprint' (or some unique value, if SOPNUMBE could actually be "reprint").  Add a @reprint variable to the main SP, which it would set to 'Y' if @shopOrder = 'reprint'.  Then @shopOrder could be set to '%' (just like when it's null).  You'd use the @reprint variable to decide whether or not to include the "*"s.  Or, now that I think about it, you could forget the @reprint variable and just use @shopOrder.  You'd just need to change the Where to recognize that 'reprint' meant "include all SOPNUMBE values" (ie. it's the same as '%').

 If that sounds good, but you need some help with the code, just let me know.

TBSupportAuthor Commented:
Hi James:

Thanks, for your great insights!

Yes, if there's anyway that you could help with the reprint syntax, that would be wonderful!  Anything that you could provide would be helpful!

I am assuming that Orders.SOPNUMBE (ie. SOP10100.SOPNUMBE) will never be "reprint".

 In the reprint SP, change

exec sp_EP_BillOfLading @reqShipDate, @shopOrder = null


exec sp_EP_BillOfLading @reqShipDate, @shopOrder = 'reprint'

 In sp_EP_BillOfLading, change the Case to
		WHEN orders.REQSHIPDATE>'1/1/1900' and @shopOrder <> 'reprint' then

Open in new window

 and change the last line in the Where from

            and orders.sopnumbe like @shopOrder


            and (@shopOrder = 'reprint' or orders.sopnumbe like @shopOrder)

 No guarantees, of course, but based on what I've seen, I think that will work.  The reprint SP passes 'reprint' for @shopOrder, instead of null.  The main SP sees that and doesn't add the "*"s, and includes all SOPNUMBE values in the Where.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.