Execute edit in a stored procedure based on database value I have code below which is set to check the date of DateToComplete, and if the date is 2 weeks or more ago, change the status of Complete from 3 to 2.

Is this the best way?

USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[spChanger] 

AS
BEGIN

Execute ('UPDATE [TblActions] SET Complete = 2 WHERE DateToComplete &lt; Date.Now.AddDays(14) AND Complete = 3' )

END
Create PROCEDURE [dbo].[spChanger] 

AS
BEGIN

Execute ('UPDATE [TblActions] SET Complete = 2 WHERE DateToComplete &lt; Date.Now.AddDays(14) AND Complete = 3' )

END



Your casing is inconsistent. If you prefer UPPERCASE keywords, stick to uppercase :)

CREATE PROCEDURE [dbo].[spChanger] 

AS
BEGIN

EXECUTE ('UPDATE [TblActions] SET Complete = 2 WHERE DateToComplete &lt; Date.Now.AddDays(14) AND Complete = 3' )

END


The name of the procedure is potentially problematic. "Changer" says nothing about what's changing, and as your database grows you'll certainly end up wondering why you didn't call it something along the lines of spUpdateTblActionsCompleteStatusCode.



Why are you executing a string? Why not just do this?

CREATE PROCEDURE [dbo].[spChanger] 

AS
BEGIN

    UPDATE TblActions
      SET Complete = 2 
    WHERE DateToComplete &lt; Date.Now.AddDays(14) 
      AND Complete = 3

END


Now you get IntelliSense in SSMS (assuming SQL Server) and it's much harder to make a typo on a column name.



I don't think this CREATE PROCEDURE script can run though. Date.Now.AddDays(14) isn't valid T-SQL.

That said I think you're missing opportunity for some parameters. I'd do it like this:

CREATE PROCEDURE [dbo].[spUpdateTblActionsCompleteStatusCode] 
    @completeStatusValue INT = 2,
    @daysDiff INT = 14,
    @completeStatusFilter INT = 3
AS
BEGIN

    UPDATE TblActions
      SET Complete = @completeStatusValue
    WHERE DATEDIFF(d, DateToComplete, DATEADD(d, @daysDiff, GETDATE())) &lt; @daysDiff
      AND Complete = @completeStatusFilter

END


When your code runs this stored procedure, if no parameters are passed it will just use the default values, and you have the flexibility to pass different parameters if/when you need to.

Also I'd recommend scripting your T-SQL as a DROP+CREATE, so the full script would look like this:

USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spUpdateTblActionsCompleteStatusCode]') AND type IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[spUpdateTblActionsCompleteStatusCode]
GO

CREATE PROCEDURE [dbo].[spUpdateTblActionsCompleteStatusCode] 
    @completeStatusValue INT = 2,
    @daysDiff INT = 14,
    @completeStatusFilter INT = 3
AS
BEGIN

    UPDATE TblActions
      SET Complete = @completeStatusValue
    WHERE DATEDIFF(d, DateToComplete, DATEADD(d, @daysDiff, GETDATE())) &lt; @daysDiff
      AND Complete = @completeStatusFilter

END