How To Return a Static Value?

I have the following stored procedure...

CREATE Procedure UserGetInfo2
(
@.UserID int, @.SystemTimePeriodID int
)
As
set nocount on
SELECT Users.UserId as UserID, Users.UserName as UserName, Users.RealName as RealName, UserTimePeriod.BudgetCode as BudgetCode, UserTimePeriod.SystemTimePeriodID as SystemTimePeriodID, Users.Password as Password, Users.SSN as SSN, Users.Location as Location, Users.ScheduleType as ScheduleType, Users.EmployeeType as EmployeeType, Users.TimeAccounted as TimeAccounted
FROM Users INNER JOIN UserTimePeriod ON Users.UserId = UserTimePeriod.UserID
WHERE (users.userID= @.UserID) AND (UserTimePeriod.SystemTimePeriodID = @.SystemTimePeriodID)
return
GO

The problem lies in that when a person has a SystemTimePeriodID over a certain value, there is no UserTimePeriod record since it has not been created yet.

Obviously, I need to wrap this in an IF...EXISTS

IF EXISTS (SELECT UserTimePeriodID FROM UserTimePeriod WHERE (SystemTimePeriodID = @.SystemTimePeriodID) AND (UserID = @.UserID))

(the SELECT above, since that's what needs to come back if the data exists)

ELSE

Do the same select but put in a static value for BudgetCode, like '0000'

GO

How could I do the part where the IF...EXISTS fails?

I'm... not sure I can use RETURNS, since it feeds into this recordset:

rstUserInfo2.Open "UserGetInfo2 " & Request("UserID") & ", " & Request("SYSTIMEPERIODID")

and later uses values from that RecordSet, such as <td><%=rstUserInfo("BudgetCode") & ""%></td>

Hi friend, I dont know if I understand you in the right way. Try this:

CREATE Procedure UserGetInfo2 (@.UserIDint, @.SystemTimePeriodIDint )As set nocount onreturnGOIFNOT EXISTS (SELECT UserTimePeriodIDFROM UserTimePeriodWHERE (SystemTimePeriodID = @.SystemTimePeriodID)AND (UserID = @.UserID))SET @.SystemTimePeriodID = 0;SELECT Users.UserIdas UserID, Users.UserNameas UserName, Users.RealNameas RealName, UserTimePeriod.BudgetCodeas BudgetCode, UserTimePeriod.SystemTimePeriodIDas SystemTimePeriodID, Users.Passwordas Password, Users.SSNas SSN, Users.Locationas Location, Users.ScheduleTypeas ScheduleType, Users.EmployeeTypeas EmployeeType, Users.TimeAccountedas TimeAccountedFROM UsersINNERJOIN UserTimePeriodON Users.UserId = UserTimePeriod.UserIDWHERE (users.userID= @.UserID)AND (UserTimePeriod.SystemTimePeriodID = @.SystemTimePeriodID)

Hope this helps.

|||No, I would need to set the outgoing BudgetCode to something, not the incoming SystemTimePeriodID.|||

It's as easy as:

Select..... , '0000' as BudgetCode, ...

|||I knew I had seen that done somewhere. Thank you for clearing up my brain's log jam.