Heres my problem, the first part selects a row from the database, if
there is no row with the criteria it inserts a row and then returns it,
the problem is the IF statement that inserts the row, never returns the
select after it. if there is a row initially in the database, it
returns the right information, I just can't get it to return the row
after inserting it. Anyone know what the problem could be?
Stored procedure:
ALTER PROCEDURE dbo.CheckCurren tPayPeriod
(@UserID varchar(50))
AS
BEGIN
-- This SP checks to see if the current PayPeriod exists,
-- if not it will create the payperiod for them and return
DECLARE @appStartDate DATETIME
DECLARE @dt DATETIME
DECLARE @rows int
SET @appStartDate = (SELECT PayPeriodStart FROM PayPeriodStart)
SET @dt = GETDATE()
SELECT
UserID
FROM
PayPeriod
WHERE
(PeriodStart <= CONVERT(varchar (10), @dt, 101)) AND (PeriodEnd >=
CONVERT(varchar (10), @dt, 101)) AND (UserID = @UserID)
-- Inserts their new PayPeriod
DECLARE @PayPeriodID int
if (@@ROWCOUNT = 0)
BEGIN
DECLARE @sDate datetime
DECLARE @eDate datetime
SET @sDate = @appStartDate
SET @eDate = DATEADD(day, 13, @sDate)
INSERT INTO
PayPeriod
(UserID, PeriodStart, PeriodEnd)
VALUES
(@UserID, @sDate, @eDate)
/*EXEC @PayPeriodID = InsertPayPeriod @UserID, @sDate, @eDate*/
SET @PayPeriodID = @@IDENTITY
SELECT * FROM PayPeriod WHERE PayPeriodID = @PayPeriodID
RETURN
END
else
RETURN
END