troubleshooting Question

Problem with SET variable statement in Stored Proc

Avatar of GeniusSteals
GeniusSteals asked on
Microsoft SQL Server 2005
3 Comments1 Solution1096 ViewsLast Modified:
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_Week' 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
	(@RetailWeek varchar(6),
	@MinDate datetime,
	@MaxDate datetime)
AS
SET @MinDate = (SELECT MIN(Date) FROM tRetailWeeks WHERE AgrWeek = @RetailWeek)
SET @MaxDate = (SELECT MAX(Date) FROM tRetailWeeks WHERE AgrWeek = @RetailWeek)
BEGIN
	-- 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
FROM 
SQLIIS.rstar.dbo.saleanalysis
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
END
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros