problem returning rows from SPROC

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.CheckCurrentPayPeriod
(@.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(dkode8@.gmail.com) writes:
> 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?

So how do you run the procedure? If you run it from Query Analyzer,
you will see something like:

UserID
--

(0 rows affected)

(1 row affected)

PayPeriodID UserID PeriodStart PeriodEnd
---- -- ---- ---
1 <value> <value> <value
(1 row affected)

If you run this from a client program, you must be able to handle these
three result sets. This means that if you use ADO - to take an example -
you should have to use .NextRecordset to navigate through the record sets.
Note here that the second record set is closed - that record sets consists
of the rowcount only.

However, it's probably better to rewrite the procedure:

CREATE PROCEDURE dbo.CheckCurrentPayPeriod (@.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 @.rowc int
DECLARE @.PayPeriodID int

SET NOCOUNT ON

SET @.appStartDate = (SELECT PayPeriodStart FROM PayPeriodStart)
SET @.dt = GETDATE()

SELECT @.PayPeriodID = PayPeriodID
FROM PayPeriod
WHERE PeriodStart <= CONVERT(char(8), @.dt, 112)
AND PeriodEnd >= CONVERT(char(8), @.dt, 112)
AND UserID = @.UserID
SELECT @.rowc = @.@.rowcount

-- Inserts their new PayPeriod
IF @.rowc = 0
BEGIN
INSERT INTO PayPeriod (UserID, PeriodStart, PeriodEnd)
VALUES (@.UserID, @.appStartDate, DATEADD(day, 13, @.sDate))
SET @.PayPeriodID = @.@.IDENTITY
END

SELECT PayPeriodId, UserId, PeriodStart, PeriodEnd
FROM PayPeriod
WHERE PayPeriodID = @.PayPeriodID
END

Observations:

o SET NOCOUNT ON removes the closed recordset for the rowcount from
the INSERT statement.

o Use style 112 when chopping of time from datetime values. 112 gives
you the format YYYYMMDD, which is always interpreted the same. Format
could be reinterpreted if the user has an unexpected language setting.

o Since @.@.rowcount is volatile - update after each statement, I catch
into a local variable immeidately, and glue that SELECT directly to
the SELECT I'm catching rowcount for.

o SELECT * in production is not good practice. Always explicitly list
which columns you want returned.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql