Dynamic datepart with DATEADD Function



CREATE TABLE [dbo].[ProcessMaster](
[ProcessID] [int] IDENTITY(1,1) NOT NULL,
[Frequency] [varchar](20) NULL,
[FrequencyQty] [int] NULL,
[LastStarted] [datetime] NULL)

insert into [ProcessMaster] (Frequency,FrequencyQty, LastStarted) 
values( 'dd', 1, '2017-06-15 08:16:20.587')
,('mi' ,5,'2010-06-22 11:12:00.537')
declare  @Frequency nvarchar(2), @FrequencyQty int, 
--more variables
@NextStart datetime , @ProcessID int=2
Select  @Frequency = Frequency, @FrequencyQty = FrequencyQty from ProcessMaster where ProcessID =  @ProcessID 

declare @sql nvarchar(2000) =N'Select  @NextStart=(Select dateadd('+@Frequency+',@FrequencyQty,LastStarted) from [dbo].[ProcessMaster] where ProcessID = @ProcessID )'

EXECUTE sp_executesql @sql, N'@NextStart datetime output,@Frequency char(2),@FrequencyQty int, @ProcessID int' ,@NextStart =@NextStart output , @Frequency =@Frequency , @FrequencyQty=@FrequencyQty , @ProcessID=@ProcessID
Select @NextStart 

drop table [ProcessMaster]




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s