Passing Parameters from Scheduled Rpt to Stored Proc

[lisa626]

Crystal XI

I have the following stored procedure which I am using as a datasource, this works great for someone if they want to manually run this. I want to be able to schedule this thru the BOE system and have it automatically emailed out to certain recipients, I want the report to pull based on the accounting period of LASTFULLWEEK. When I try this it doesn't work, I am assuming it's because the SP wants you to specify dates up front, is there a way to do this??

CREATE PROCEDURE RPT_LeisureAgentPro
@startdate datetime,
@enddate datetime

AS

set nocount on

SELECT
NA.NONAIR_ID AS ID,
NA.PASSENGER_NAME AS PASSENGER_NAME,
NA.TICKET_DATE AS TICKET_DATE,
NA.PNR_LOCATOR AS PNR_LOCATOR,
NA.BOOK_CITY AS BOOK_CITY,
NA.BOOK_AGENT AS BOOK_AGENT,
NA.COMMISSION_AMT AS COMMISSION_AMT,
NA.TOTAL_FARE AS TOTAL_FARE,
NA.VENDOR_CODE AS VENDOR_CODE,
NA.VENDOR_REF# AS VENDOR,
A.BRANCH_ID,
A.FIRST_NAME,
A.LAST_NAME
FROM [APP-STL-178].CASTLE.dbo.NonAir NA
LEFT JOIN [APP-STL-178].CASTLE.dbo.Tickets T ON NA.Invoice# = T.Invoice#
LEFT JOIN [app-stl-178].CASTLE.dbo.AGENT A ON A.AGENT_ID = NA.BOOK_AGENT
WHERE NA.Ticket_Date BETWEEN @startdate AND @enddate
AND (NA.Book_Agent IN ('TA','PC','KG','JS','BW','VF','JL','PS','TY') AND NA.Company_Code LIKE '1%'
OR NA.Company_Code LIKE '9%')
UNION ALL
SELECT
T.TICKET# as ID,
T.PASSENGER_NAME AS PASSENGER_NAME,
T.TICKET_DATE AS TICKET_DATE,
T.PNR_LOCATOR AS PNR_LOCATOR,
T.BOOK_CITY AS BOOK_CITY,
T.BOOK_AGENT AS BOOK_AGENT,
T.COMMISSION_AMT AS COMMISSION_AMT,
T.TOTAL_FARE AS TOTAL_FARE,
T.VENDOR_CODE AS VENDOR_CODE,
T.ARC_CARRIER AS Vendor,
A.BRANCH_ID,
A.FIRST_NAME,
A.LAST_NAME
FROM [APP-STL-178].CASTLE.dbo.Tickets T
LEFT JOIN [APP-STL-178].CASTLE.dbo.NonAir NA ON NA.Invoice# = T.Invoice#
LEFT JOIN [app-stl-178].CASTLE.dbo.AGENT A ON A.AGENT_ID = T.BOOK_AGENT
WHERE T.Ticket_Date BETWEEN @startdate AND @enddate
AND (T.Book_Agent IN ('TA','PC','KG','JS','BW','VF','JL','PS','TY') AND T.Company_Code LIKE '1%'
OR T.Company_Code LIKE '9%')
AND T.REFUND_FLAG NOT IN ('V')







GO

 

[synapsevampire]

Your SP states this:

BETWEEN @startdate AND @enddate

So you need to rewrite the SP.

A classic means of handling this is to pass it a dummy date that a user would never select, such as 1/1/1900

Then in the SP check to see if it's passed that date, and if so, then default to using the last full week dates, which you would compute from the current date in the SQL.

Since you didn't post your dtaabase type, and SQL varies based on the databse, I won't guess at the syntax.

You're better served to take the theory I described and post in the proper database forum anyway, as the issue is best resolved in the SP.

-k