Declaring PROCEDURE with dynamic variable based on user input

Question

IF EXISTS ( SELECT *
    FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_SCHEMA = N'dbo'
    AND SPECIFIC_NAME = N'Reports'
    AND ROUTINE_TYPE = N'PROCEDURE')
    DROP PROCEDURE dbo.Reports        
GO

CREATE PROCEDURE dbo.Reports (@Date_of_Purchase DATE)
AS
BEGIN
    SELECT o.O_Id, o.Sale_Price, o.Private_Band, c.Type, c.Points_Issued, o.Date_of_Purchase
    FROM Order_Details o
    LEFT JOIN Transaction_Historys c ON o.Date_of_Purchase = c.Date_of_Purchase
    WHERE o.Date_of_Purchase > @Date_of_purchase
END
GO

EXECUTE dbo.Reports
GO

I want to make o.Date_of_Purchase > DATEADD(d,@date,getdate()). @date should be dynamic values based on user input, how to declare the days(@date) dynamically.

I get an error when executing the stored procedure:

Must declare the scalar variable

Can u pls tell me help me now.@date should get dynamic valus from user and return the select statement records — sai, May 25 '20 at 10:18
Your procedure has a parameter, so what's the problem here? Apart from that you call the procedure inside itself, without said parameter (or *is* that the problem?) — Thom A, May 25 '20 at 10:45
I get an error must-declare-the-scalar-variable-error-for-stored-procedure — sai, May 25 '20 at 10:49
I shuld get the records whose dateofppurchase value is lessthan dynamic value like less than 90days,120dyas like that — sai, May 25 '20 at 10:52
This `o.Date_of_Purchase > DATEADD(d,@date,getdate())` gives records 90 days in the future more beyond. Is that what you want? Or do you want the last 90 days? — Dale K, May 25 '20 at 11:12
Perhaps Erland's discussion of [dynamic search conditions](http://www.sommarskog.se/dyn-search.html) will give you some inspiration. — SMor, May 25 '20 at 13:01

Dale K · Answer 1 · 2020-05-25T11:33:29.683

Here is how to create and run a procedure which takes a parameter for days, adds those days to today's date and then carries out a query filtering against that date.

The main point is you need to create, initialise, and pass in the parameters.

CREATE PROCEDURE dbo.Reports
(
    @Days INT
)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT o.O_Id, o.Sale_Price, o.Private_Band, c.Type, c.Points_Issued, o.Date_of_Purchase
    FROM Order_Details o
    LEFT JOIN Transaction_Historys c ON o.Date_of_Purchase = c.Date_of_Purchase
    WHERE o.Date_of_Purchase > DATEADD(DAY, @Days, CURRENT_TIMESTAMP);

    RETURN 0;
END
GO

-- Create and initialise parameter.
DECLARE @Days INT = 90;

-- Execute SP, passing in our parameter
EXECUTE dbo.Reports @Days;

Declaring PROCEDURE with dynamic variable based on user input

1 Answers1