SQL version of immediate if - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL version of immediate if

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-04 : 11:49:58

Jay writes "Not sure if this is a stumper but as a relative newbie it has me stumped. I am returning years and months - however if years = 0 I don't want to display the string " years, " (the code below should explain what I mean). I need to do this within the stored procedure as opposed to the web page. Any pointers would be eternally appreciated!

CREATE PROCEDURE QGetHumanR
@Emp_No int
as
SELECT HR2.HumanRId AS [Employee no], [HR2].[Firstname] + ' ' + [HR2].[surname] AS Name,
HRGroups.description AS [Group], HRPositions.description AS [Position], HR2.STATUS AS Status, HR2.natureOfEmp AS Type,
cast([hr2].[YearsService] as varchar(10)) + ' Years, ' + cast([hr2].[MonthsService] as varchar(10)) + ' Months ' AS [Length of Service]
FROM (((HumanR AS HR2 LEFT JOIN HRGroups ON HR2.GroupCode = HRGroups.HRGroupsId)
LEFT JOIN HRPositions ON HR2.PositionCode = HRPositions.HRPositionsId) LEFT JOIN HRUnits ON HR2.UNITCODE = HRUnits.HRUnitsId)
RIGHT JOIN (HumanR AS HR1 LEFT JOIN HumanRSecurity ON HR1.HumanRId = HumanRSecurity.Employee) ON HR2.HumanRId = HumanRSecurity.CanSee
WHERE HR1.HumanRId=@Emp_No
GO

The 2 CAST values are the important bits here. If hr2.yearsservice = 0 then don't display the 0 or the string " years, " - ditto for the months."

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-04 : 11:53:46

cast([hr2].[YearsService] as varchar(10)) + ' Years, ' + cast([hr2].[MonthsService] as varchar(10)) + ' Months ' AS [Length of Service]

change this to a case statement

[edit] bleh, lemme fix this
[edit2]
This looks better
SELECT HR2.HumanRId AS [Employee no], [HR2].[Firstname] + ' ' + [HR2].[surname] AS Name,
HRGroups.description AS [Group], HRPositions.description AS [Position], HR2.STATUS AS Status, HR2.natureOfEmp AS Type,
Case
When YearsService <> 0 then cast([hr2].[YearsService] as varchar(10)) + ' Years, ' + cast([hr2].[MonthsService] as varchar(10)) + ' Months '
else ''
end as 'length of service'
---Put your from statement and all its joins here

-----------------------
Take my advice, I dare ya

Edited by - M.e. on 07/04/2002 11:55:07

Edited by - M.e. on 07/04/2002 11:58:13

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources