Hi again..today's problem comes from trying to assign a couple of variables based on the value of another variable. In this instance, the variable @RetailWeek is passed to the procedure which should then use this to work out the MinDate and MaxDate for that week referenced in a table.
I see lots of code about but not sure where I am meant to put it in respect of the ALTER PROCEDURE statement. When I try to execute the SP I get the error message:
Procedure or Function 'proc_Update_OP_Single_Wee
k' expects parameter '@MinDate', which was not supplied.
I'll add that I have put the SET statements before AS, after AS (as they are now) and in the main --Insert statements section...
ALTER PROCEDURE [dbo].[proc_Update_OP_Single_Week]
-- Add the parameters for the stored procedure here
SET @MinDate = (SELECT MIN(Date) FROM tRetailWeeks WHERE AgrWeek = @RetailWeek)
SET @MaxDate = (SELECT MAX(Date) FROM tRetailWeeks WHERE AgrWeek = @RetailWeek)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT Right(sales_person,5) AS SalesPer5Char,branch_code,reg_no,tran_no,tran_date,SUM(sales_qty) AS Quantity,SUM(sales_value) AS Net_Amount, SUM(tax_value) AS Tax_Amount
WHERE sales_value<>0 AND tran_date>@MinDate AND tran_Date<@MaxDate
GROUP BY Right(Sales_person,5), branch_code, reg_no, tran_no, tran_date
ORDER BY tran_date