CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
DBA
DBA

Reputation: 21

Replace Getdate() with stored procedure

We have a job with couple of steps and almost all of the steps use getdate(), but instead we want to get the date from a specific table and column. The table includes only two columns status as ready (doesn't change) and statusdate (dynamic). The plan is to create a stored procedure and replace the getdate() with that stored procedure.

How do I write the stored procedure? How do I declare a variable? 

CREATE PROCEDURE SP_DATE
    @StatusDate DATETIME
AS 
BEGIN
    SELECT StatusDate 
    FROM [DB_Name].[dbo].[Table_Name]
    WHERE status = ready 
END

Thank you!

Upvotes: 1

Views: 1188

Answers (2)

Mx.Wolf
Mx.Wolf

Reputation: 678

Your jobs use getdate() function therefore in order to replace it with custom programmatic object you should use function as well and not a stored procedure. With a function like this

CREATE FUNCTION dbo.StatusDate ()
RETURNS DATETIME
AS
BEGIN
  RETURN (SELECT
      StatusDate
    FROM Table_Name
    WHERE status = 'ready')
END

you can replace getdate directly

SELECT
  id
FROM Your_Job_List yjl
WHERE yjl.aDate < dbo.StatusDate()--getdate()

yet there are some questions to the design. One biggest single task of RDBMS is joining tables and perhaps a query similar to next one might be better

SELECT
  id
FROM Your_Job_List yjl
    ,Table_Name tn
WHERE yjl.aDate < tn.StatusDate
AND tn.status = 'ready'

Upvotes: 1

Sql Surfer
Sql Surfer

Reputation: 1422

CREATE PROCEDURE spRunNextDate
AS 
BEGIN

  --SET NOCOUNT ON 

   declare @runDate datetime 
   select @runDate = MIN(StatusDate)
     from [DB_Name].[dbo].[Table_Name] 
    where [status] = 'ready'

   IF (@runDate IS NULL) 
   BEGIN
      PRINT 'NO Dates in Ready State.'
      RETURN 0
   END

   PRINT 'Will Run Date of '  + CAST(@runDate as varchar(20)) 

   -- Notice the MIN  or MAX usage above to get only one date per "run"


END      
GO

There are huge holes and questions raised in my presumptuous sp above, but it might get you to thinking about why your question implies that there is no parameter. You are going to need a way to mark the day "done" or else it will be run over and over.

Upvotes: 0

Related Questions