SQL2000 - IF THEN ELSE

[#1]

i have a fairly large stored procedure that essentialy just displays a list of results based on the value of 3 variables.
the main part of the script remains the same, it's the WHERE part that will change depending on the variables chosen.

at the moment i just have it set to to lots of IF THEN's. this means that the resulting stored procedure is pretty large as there are about 20 different outcome possibilites.

I was wondering if this could be simplified so that only the WHERE section is inside the IF?

at the moment it is like this

declare @subcon varchar(13)
declare @filter varchar(30)
declare @jobtype varchar(30)

set @subcon = 'JSH'
set @filter = 'TODAY'
set @jobtype = 'ALL'

---- all jobs today ----

IF @filter = 'TODAY' and @jobtype = 'ALL'
BEGIN

SELECT

Call_Ref,
last_event_status,
CASE
WHEN call_type in ('B','PB','RC','FS') and Per_Data31 = 'SUB' THEN 'brk-subpart'
WHEN call_type in ('B','PB','RC','FS') and Per_Data31 = 'CTS' THEN 'brk-ctspart'
WHEN call_type in ('H3','PI','I','SI','NI','SC') THEN 'insp'
WHEN call_type in ('HB','PM','HS','HL','SL','NS') THEN 'asv'
END AS 'urllink',
CASE
WHEN call_type in ('B','PB','RC','FS') THEN 'B'
WHEN call_type in ('H3','PI','I','SI','NI','SC') THEN 'I'
WHEN call_type in ('HB','PM','HS','HL','SL','NS') THEN 'A'
END AS 'jobcheck',
Call_Type,

CASE
WHEN call_type in ('PB','B') then 'BREAKDOWN'
WHEN call_type in ('RC') then 'BREAKDOWN REPEAT CALL'
WHEN call_type in ('HB') then 'BOILER SERVICE'
WHEN call_type in ('PI') then 'BOILER INSPECTION & SERVICE'
ELSE UPPER(Call_Type_Description) END AS Call_Type_Description,

UPPER(ISNULL(CONVERT(nvarchar(50),Scheduled_Date_Time),'Not Scheduled')) Sch_Date,
UPPER(ISNULL(Co_Name,'')) Co_Name,
UPPER(ISNULL(Add1,'')) Add1,
UPPER(ISNULL(Add2,'')) Add2,
UPPER(ISNULL(Add3,'')) Add3,
UPPER(ISNULL(Add4,'')) Add4,
UPPER(ISNULL(Post_Code,'')) Post_Code,
UPPER(ISNULL(Tel_No,'')) Tel_No,
UPPER(ISNULL(Tel_No2,'')) Tel_No2, 
UPPER(ISNULL(Tel_No3,'')) Tel_No3,
UPPER(Contract_Name) as Contract_Name, link_to_Contract_header,
CALLM_Data28,
CASE WHEN call_type IN ('PB','B','RC','FS') THEN UPPER(CONVERT(NVARCHAR(50),CONVERT(CHAR(3), CONVERT(DATETIME,CALLM_Data28),0)) + ' ' + CONVERT(NVARCHAR(50),DATEPART(DAY,convert(DATETIME,CALLM_Data28))) + ' ' + CONVERT(NVARCHAR(50),DATEPART(YEAR,convert(DATETIME,CALLM_Data28)))) ELSE NULL END partseta,
City_Buys_Parts,
CASE WHEN Fixed_Rate = 1 THEN 'Fixed Rate' ELSE 'Time and Materials' END AS Fixed_Rate,
CASE WHEN Contract_Group = 'D00084' AND (CLM_Data17 IS NULL OR CLM_Data17 = '') THEN 'Scottish Power' ELSE '' END SP, 
CASE
WHEN DATEDIFF(DAY, getDate(), (Date_Received + 45)) < 0 THEN 0
WHEN DATEDIFF(DAY, getDate(), (Date_Received + 45)) BETWEEN 30 AND 45 THEN 3
WHEN DATEDIFF(DAY, getDate(), (Date_Received + 45)) BETWEEN 15 AND 29 THEN 2
WHEN DATEDIFF(DAY, getDate(), (Date_Received + 45)) BETWEEN 0 AND 14 THEN 1 END AS SLA,
Pers_SubCon_No_Reallocate,
clm_data28,
clm_data29,
clm_data30,
clm_data31,
clm_data32,
clm_data33,
UPPER(CONVERT(varchar(3000),problem)) as problem

FROM Calls WITH (NOLOCK)
INNER JOIN Personnel WITH (NOLOCK) ON Pers_Ref = @subcon
LEFT JOIN Personnel_More WITH (NOLOCK) ON per_link_to_pers_ref = @subcon
INNER JOIN Clients WITH (NOLOCK) ON Link_to_Client = Client_Ref
INNER JOIN LU_Call_Types WITH (NOLOCK) ON Call_Type = Call_Type_Code
LEFT JOIN ContractIds WITH (NOLOCK) ON Contract_Ref = Link_to_COntract_Header
LEFT JOIN Clients_More WITH (NOLOCK) ON CLM_Link_to_Client = Client_Ref
LEFT JOIN Call_More WITH (NOLOCK) ON Callm_Link_to_Call = Call_Ref

WHERE
	(Last_Allocated_To = @subcon
	and Last_Event_Status = 'A'
	AND dbo.DateOnly(Scheduled_Date_Time) = dbo.DateOnly(getDate())
	and call_type in ('B','PB','RC','FS','H3','PI','I','SI','NI','SC','HB','PM','HS','HL','SL','NS'))
	OR
	(Last_Allocated_To = @subcon
	and Last_Event_Status = 'A'
	AND dbo.DateOnly(CALLM_Data28) = dbo.DateOnly(getDate())
	AND (dbo.DateOnly(Scheduled_Date_Time) < dbo.DateOnly(CALLM_Data28))
	AND ((dbo.DateOnly(Scheduled_Date_Time) >= dbo.DateOnly(CALLM_Data28)) OR  dbo.DateOnly(CALLM_Data28) IS NULL)
	and call_type in ('B','PB','RC','FS'))
	
ORDER BY scheduled_date_time, add1, call_type_description

END

---- all breakdowns today ----

IF @filter = 'TODAY' and @jobtype = 'BREAKDOWN'
BEGIN

SELECT

Call_Ref,
last_event_status,
CASE
WHEN call_type in ('B','PB','RC','FS') and Per_Data31 = 'SUB' THEN 'brk-subpart'
WHEN call_type in ('B','PB','RC','FS') and Per_Data31 = 'CTS' THEN 'brk-ctspart'
WHEN call_type in ('H3','PI','I','SI','NI','SC') THEN 'insp'
WHEN call_type in ('HB','PM','HS','HL','SL','NS') THEN 'asv'
END AS 'urllink',
CASE
WHEN call_type in ('B','PB','RC','FS') THEN 'B'
WHEN call_type in ('H3','PI','I','SI','NI','SC') THEN 'I'
WHEN call_type in ('HB','PM','HS','HL','SL','NS') THEN 'A'
END AS 'jobcheck',
Call_Type,
CASE
WHEN call_type in ('PB','B') then 'BREAKDOWN'
WHEN call_type in ('RC') then 'BREAKDOWN REPEAT CALL'
WHEN call_type in ('HB') then 'BOILER SERVICE'
WHEN call_type in ('PI') then 'BOILER INSPECTION & SERVICE'
ELSE UPPER(Call_Type_Description) END AS Call_Type_Description,
UPPER(ISNULL(CONVERT(nvarchar(50),Scheduled_Date_Time),'Not Scheduled')) Sch_Date,
UPPER(ISNULL(Co_Name,'')) Co_Name,
UPPER(ISNULL(Add1,'')) Add1,
UPPER(ISNULL(Add2,'')) Add2,
UPPER(ISNULL(Add3,'')) Add3,
UPPER(ISNULL(Add4,'')) Add4,
UPPER(ISNULL(Post_Code,'')) Post_Code,
UPPER(ISNULL(Tel_No,'')) Tel_No,
UPPER(ISNULL(Tel_No2,'')) Tel_No2, 
UPPER(ISNULL(Tel_No3,'')) Tel_No3,
UPPER(Contract_Name) as Contract_Name, link_to_Contract_header,
CALLM_Data28,
CASE WHEN call_type IN ('PB','B','RC','FS') THEN UPPER(CONVERT(NVARCHAR(50),CONVERT(CHAR(3), CONVERT(DATETIME,CALLM_Data28),0)) + ' ' + CONVERT(NVARCHAR(50),DATEPART(DAY,convert(DATETIME,CALLM_Data28))) + ' ' + CONVERT(NVARCHAR(50),DATEPART(YEAR,convert(DATETIME,CALLM_Data28)))) ELSE NULL END partseta,
City_Buys_Parts,
CASE WHEN Fixed_Rate = 1 THEN 'Fixed Rate' ELSE 'Time and Materials' END AS Fixed_Rate,
CASE WHEN Contract_Group = 'D00084' AND (CLM_Data17 IS NULL OR CLM_Data17 = '') THEN 'Scottish Power' ELSE '' END SP, 
CASE
WHEN DATEDIFF(DAY, getDate(), (Date_Received + 45)) < 0 THEN 0
WHEN DATEDIFF(DAY, getDate(), (Date_Received + 45)) BETWEEN 30 AND 45 THEN 3
WHEN DATEDIFF(DAY, getDate(), (Date_Received + 45)) BETWEEN 15 AND 29 THEN 2
WHEN DATEDIFF(DAY, getDate(), (Date_Received + 45)) BETWEEN 0 AND 14 THEN 1 END AS SLA,
Pers_SubCon_No_Reallocate,
clm_data28,
clm_data29,
clm_data30,
clm_data31,
clm_data32,
clm_data33,
UPPER(CONVERT(varchar(3000),problem)) as problem

FROM Calls WITH (NOLOCK)
INNER JOIN Personnel WITH (NOLOCK) ON Pers_Ref = @subcon
LEFT JOIN Personnel_More WITH (NOLOCK) ON per_link_to_pers_ref = @subcon
INNER JOIN Clients WITH (NOLOCK) ON Link_to_Client = Client_Ref
INNER JOIN LU_Call_Types WITH (NOLOCK) ON Call_Type = Call_Type_Code
LEFT JOIN ContractIds WITH (NOLOCK) ON Contract_Ref = Link_to_COntract_Header
LEFT JOIN Clients_More WITH (NOLOCK) ON CLM_Link_to_Client = Client_Ref
LEFT JOIN Call_More WITH (NOLOCK) ON Callm_Link_to_Call = Call_Ref

WHERE Last_Allocated_To = @subcon
AND Last_Event_Status = 'A'
AND dbo.DateOnly(Scheduled_Date_Time) = dbo.DateOnly(getDate())
AND ((dbo.DateOnly(Scheduled_Date_Time) >= dbo.DateOnly(CALLM_Data28)) OR  dbo.DateOnly(CALLM_Data28) IS NULL)
and call_type in ('RC','PB','B','FS')

ORDER BY scheduled_date_time, add1, call_type_description

END

.... and so on......

[#2]

SQL 2000 really?! 

One thing you could do is construct the statement as a string then use exec sp_executesql to run the command.

[#3]

yes really.
the sql 2000 server works, it's production, and there really is no need to tamper with it at present.

[#4]

ideall what i'm looking for is something like...

select blah blah
from blah
---
IF @filter = 'TOMORROW' and @jobtype = 'BREAKDOWN'
   where
   blah blah
elseif @filter = 'TODAY' and @jobtype = 'BREAKDOWN'
   where
   blah blah
END

order by blah

[#5 · Accepted solution]

If there's no need to tamper why are you messing around with this stored procedure   It's been so long since I've used SQL2000 I don't know what commands may not be available in that version so some of this may not work...

You can't have an if statement inside the where statement so you'd have to approach it differently.  Probably the best way would be to try and set the values for the where clause first before the statement.  So for example where you have call_type in ('B','PB','RC','F' .... You could create a table variable for the values before the select statement. Something like this:

declare @tableVariable as table(id int)

IF @filter = 'TOMORROW' and @jobtype = 'BREAKDOWN'

BEGIN 

insert into @tableVariable values (3);

insert into @tableVariable values (4);

END

ELSE IF @filter = 'TODAY' and @jobtype = 'BREAKDOWN'

BEGIN 

insert into @tableVariable values (5);

insert into @tableVariable values (6);

END

select * from table

where id in (select id from @tableVariable)

The other option is to build the complete statement as a string and use the sp_executesql sp I mentioned (if this is available in SQL 2000..) So you would do something like this:

declare @selectStatement as nvarchar(max)
declare @whereStatement as nvarchar(max)
declare @combinedStatement as nvarchar(max)

set @selectStatement = 'select blah blah
from blah'
---
IF @filter = 'TOMORROW' and @jobtype = 'BREAKDOWN'
   set @whereStatement = '
   where
   blah blah'
ELSE IF @filter = 'TODAY' and @jobtype = 'BREAKDOWN'
   set @whereStatement = '
   where
   blah blah'

set @combinedStatement = @selectStatement + @whereStatement

exec sp_executesql @combinedStatement

[#6]

no tampering with the server, but stored procedures can be tweeked to make them easier to maintain.
the first option is a no-go.
the second option would be the way to go.
basically the core of the script stays the same, and it's just the WHERE that changes depending on what the user selectable variables are

[#7]

@chenks76 - Use the CASE predicate within the WHERE clause this may be what you're after.

[#8]

i thought about case, but i believe IF THEN ELSE is the better way to deal with it?

[#9]

The use of CASE can be ugly if there are a lot of conditions to account for, but for this I think it would suffice, but that's your call. Using IF ELSE can obviously be done, but would be better served using a dynamic query as suggested by @dp106

[#10]

got a working basic version.

ps - this is just a basic script to test the functionality. it does not respresent the complexity of the actual script that will be used.

declare @selectStatement as nvarchar(4000)
declare @whereStatement as nvarchar(4000)
declare @combinedStatement as nvarchar(4000)
declare @filter as varchar(255)

set @filter = 'ngl'

set @selectStatement = 'select portal.*
from cportal
inner join calls with (nolock) on callref=call_ref'

IF @filter = 'private'
	set @whereStatement = ' where updatedwhen is not null
			       and contract = ''CTSP''
			       and last_Event_status in (''RD'',''C'')
			       order by callref desc'
ELSE IF @filter = 'sperl'
	set @whereStatement = ' where updatedwhen is not null
				and contract = ''SPE''
				and last_Event_status in (''RD'',''C'')
				order by callref desc'
ELSE IF @filter = 'ngla'
	set @whereStatement = ' where updatedwhen is not null
				and contract = ''NGL''
				and last_Event_status in (''RD'',''C'')
				order by callref desc'

set @combinedStatement = @selectStatement + @whereStatement

exec sp_executesql @combinedStatement

[#11]

Hmm so I don't think sp_executesql came in until SQL 2005 so that's probably out.  It also might not be great performance wise as sql will probably create a new execution plan every time.

There are some good examples here which I think are perhaps a better option including using case in the where clause

[#12]

it works fine with sp_executesql

[#13]

---

@Anonymous wrote:

The use of CASE can be ugly if there are a lot of conditions to account for, but for this I think it would suffice, but that's your call. Using IF ELSE can obviously be done, but would be better served using a dynamic query as suggested by @dp106

---

Yep in my experience, if there are many you need to make sure they are in the right order, otherwise one condition you expect it to fall though can be caught by one before it.