SQLTeam.com | Weblogs | Forums

Stored Procedure with two parameters

sql2008r2

#1

SELECT ORDER_NO, SUBSYSTEM, PRODUCT_CODE, INVOICE_DATE, CYCLE_BEGIN_DATE, CYCLE_END_DATE, RATE_STRUCTURE, RATE_CODE, LINE_STATUS_CODE, FULFILL_STATUS_CODE,
ACTUAL_TOTAL_AMOUNT
FROM ORDER_DETAIL
WHERE (SUBSYSTEM = 'MBR') AND (PRODUCT_CODE LIKE 'SOC%') AND (CYCLE_BEGIN_DATE = CONVERT(DATETIME, '2017-01-01 00:00:00', 102)) AND (RATE_CODE = 'RENEW')

I need to create a store procedure for crystal report with two parameters CycleBeginDate and Invoice date. Above query results for only 2017 year. Members will be given membership every year. Even user entered dates like middle of month, it should give members till that date.


#2
CREATE PROC dbo,CrystalReportProc (@CycleBeginDate date, @InvoiceDate date) AS
BEGIN
SELECT ORDER_NO, SUBSYSTEM, PRODUCT_CODE, INVOICE_DATE, CYCLE_BEGIN_DATE, CYCLE_END_DATE, RATE_STRUCTURE, RATE_CODE, LINE_STATUS_CODE, FULFILL_STATUS_CODE, 
ACTUAL_TOTAL_AMOUNT
FROM ORDER_DETAIL
WHERE (SUBSYSTEM = 'MBR') AND (PRODUCT_CODE LIKE 'SOC%') AND (CYCLE_BEGIN_DATE = @CycleBeginDate  AND INVOICE_DATE = @InvoiceDate AND (RATE_CODE = 'RENEW')
END

#3

I'm sure its obvious to O/P, but in case not, that "," should be a "."

If the CYCLE_BEGIN_DATE column is DateTime datatype and IF it contains a time element then the comparison with DATE datatype won't match, sadly.


#4

good catch on the comma K! As for the check, well we don't know the column type. I made the (absurd?) assumption that it is the same as the parameter type. Hopefully the OP will post some more info