CODEWITHSUNDEEP
sqlsql-serverif-statementssrs-2008
Darko Milic
Darko Milic

Reputation: 189

IF clause in SQL procedure

I created dates table with all dates in next 15 years... 

CREATE TABLE [dbo].[All_Dates](
    [Year] [int] NOT NULL,
    [Quarter] [nvarchar](6) NOT NULL,
    [Month] [nvarchar](6) NOT NULL,
    [Week] [nvarchar](7) NOT NULL,
    [Day] [nvarchar](50) NOT NULL,
    [DayKey] [tinyint] NOT NULL,
    [DateName] [nvarchar](50) NOT NULL,
    [DateKey] [date] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [DateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Purpose of that is to use that time periods in some of procedures for reporting. So, problem is here now, when I need to have some IF clause on start of procedure, when parameters are needed because I need to give choice to customers to choose between monthly or quarterly report. For monthly I have code: 

ALTER procedure [dbo].[proc1]

@dt date

as

declare @startdate date
        ,@enddate date
SET @StartDate = (select min(datekey) from TDW.dbo.All_Dates where month = convert(varchar(6),@dt,112))
SET @EndDate = (select max(datekey) from TDW.dbo.All_Dates where month = convert(varchar(6),@dt,112))

So, I need something like if time_range=quarter then...

Upvotes: 1

Views: 54

Answers (1)

Peter B
Peter B

Reputation: 24147

Assuming that '201701' etc is a valid value for your month column, then you should work with start and end months, and then find the dates for those. Here is an example how:

DECLARE @dt DATE = '2017-01-01'
DECLARE @time_range VARCHAR(10) = 'quarter'

DECLARE @startmonth VARCHAR(6) = convert(VARCHAR(6), @dt, 112) -- e.g. '201701'
IF @time_range = 'quarter' SET @dt = DATEADD(m, 2, @dt) -- Assumed it is OK to change @dt
DECLARE @endmonth   VARCHAR(6) = convert(VARCHAR(6), @dt, 112) -- e.g. '201703'

DECLARE @startdate DATE, @enddate DATE
SET @StartDate = (select min(datekey) from TDW.dbo.All_Dates where month = @startmonth)
SET @EndDate   = (select max(datekey) from TDW.dbo.All_Dates where month = @endmonth)

If needed you could use multiples IFs, or even a CASE WHEN ... THEN ... ELSE ... END statement.

Upvotes: 1

Related Questions