CODEWITHSUNDEEP
sql
user2119980
user2119980

Reputation: 509

Changing Parameter Values in Stored Procedures

I have a stored procedure for a view that is so massive it always times out, it is used to find data for certain date ranges. This is an entirely new concept to me, I have the stored procedure set up for the main date range, I just cant figure out how to Execute it properly if I need specific dates. Here is the code and issue

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[COL_Run_DOM_Parameters]
@StartDate varchar (50),
@EndDate varchar (50)
AS
SET NOCOUNT ON
SELECT *
FROM dbo.COL_V_GEMS_DOM_FCT
WHERE REC_EFF_STT_DT BETWEEN '2010-01-01' AND '2012-12-31'

When I execute I do it like:

Execute COL_Run_DOM_Parameters @StartDate = '2011-12-22', @EndDate '2012-05-17'

But when I execute it still gives me all the data between 2010 and 2012 instead of the date range I asked for. Where in my code is there a mistake?

Upvotes: 1

Views: 3973

Answers (3)

Arpit
Arpit

Reputation: 126

Change the query to

    SELECT *
    FROM dbo.COL_V_GEMS_DOM_FCT
   WHERE REC_EFF_STT_DT BETWEEN @StartDate AND @EndDate

and call the SP like

Declare @StartDate = '2012-02-01'
Declare @EndDate = '2013-02-01'
EXEC COL_Run_DOM_Parameters @StartDate @EndDate

Upvotes: 1

elopez
elopez

Reputation: 109

SELECT * FROM dbo.COL_V_GEMS_DOM_FCT WHERE REC_EFF_STT_DT BETWEEN '2010-01-01' AND '2012-12-31'

you have hardcoded the dates my friend , you are not using your variables

Upvotes: 1

Derek
Derek

Reputation: 23318

You need to change your query to reference the parameters!

ALTER PROCEDURE [dbo].[COL_Run_DOM_Parameters]
  @StartDate varchar (50),
  @EndDate varchar (50)
AS
SET NOCOUNT ON
SELECT *
FROM dbo.COL_V_GEMS_DOM_FCT
WHERE REC_EFF_STT_DT BETWEEN @StartDate and @EndDate

Execute just like you have been.

Upvotes: 5

Related Questions