January 5, 2002 at 1:00 pm
I have a stored procedure that returns a result set to my application. Within it I want to do another select to get a code that I need for the stored procedure. When I use a nested stored procedure to select the code the ultimate result set I want doesn't seem to be returned to my application, but rather the select for the code is returned. When I use a straight select to get the code (rather than a sproc) it seems to work fine.
Can anyone help me out here?
January 5, 2002 at 1:24 pm
Does your nested stored procedure return an output parameter?
January 5, 2002 at 3:20 pm
Can you post the code behind your two stored procedures?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 7, 2002 at 10:22 am
Yes. I am returning an output parameter from the nested procedure.
Here is the code:
CREATE PROCEDURE CP_GetCalendarDates
@EmployeeID INT,
@CaseID INT,
@CustomerID INT
AS
BEGIN
DECLARE
@TransDutyCode INT
CREATE TABLE #CalendarTempTable
(
EventDate datetime,
EventType varchar(15),
ShortDescription varchar(25),
LongDescription varchar(60),
PFirstName varchar(50),
PLastName varchar(80),
PSpecialty varchar(80)
)
BEGIN
DECLARE @ret INT
EXEC CP_GETTRANSDUTYREASONCODE @CustomerID, @ret OUTPUT
SELECT @ret "TransDutyCode"
SET @TransDutyCode = @ret
END
-- ATTENDANCE DATA - Get attendance records into cursor
DECLARE @CFirstDayOff DATETIME, @CEndDate DATETIME, @CReasonID INT, @CStatus INT
-- Set up the cursor
DECLARE attendance_cursor CURSOR FOR
SELECT FirstDayOff, EndDate, ReasonID, Status
FROM Attendance
WHEREEmployeeID = @EmployeeID
ANDLinkToCase = @CaseID
ANDCustomerID = @CustomerID
ORDER BY FirstDayOff DESC
OPEN attendance_cursor
-- Perform the first fetch and store the values in variables.
FETCH NEXT FROM attendance_cursor
INTO @CFirstDayOff, @CEndDate, @CReasonID, @CStatus
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Insert cursor items into temp table
IF @CFirstDayOff IS NOT NULL
IF @CReasonID <> @TransDutyCode
INSERT INTO #CalendarTempTable
(EventDate,
EventType,
ShortDescription,
LongDescription,
PFirstName,
PLastName,
PSpecialty)
VALUES(
@CFirstDayOff,
'AbsStart',
'',
'Started Absence',
NULL,
NULL,
NULL)
IF @CEndDate IS NOT NULL
BEGIN
IF @CStatus = 1
INSERT INTO #CalendarTempTable
(EventDate,
EventType,
ShortDescription,
LongDescription,
PFirstName,
PLastName,
PSpecialty)
VALUES(
@CEndDate,
'RTWFull',
'',
'Actual Return to Work on Full Duty',
NULL,
NULL,
NULL)
IF @CStatus = 2
INSERT INTO #CalendarTempTable
(EventDate,
EventType,
ShortDescription,
LongDescription,
PFirstName,
PLastName,
PSpecialty)
VALUES(
@CEndDate,
'RTWTrans',
'',
'Actual Return to Work on Transitional Duty',
NULL,
NULL,
NULL)
IF @CStatus = 3
INSERT INTO #CalendarTempTable
(EventDate,
EventType,
ShortDescription,
LongDescription,
PFirstName,
PLastName,
PSpecialty)
VALUES(
@CEndDate,
'RTWPerm',
'',
'Actual Return to Work on Permanent Alternate Duty',
NULL,
NULL,
NULL)
END
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM attendance_cursor
INTO @CFirstDayOff, @CEndDate, @CReasonID, @CStatus
END
CLOSE attendance_cursor
DEALLOCATE attendance_cursor
-- Select all items from temp table and order by date for display
SELECT DISTINCT EventDate, EventType, ShortDescription, LongDescription, PFirstName, PLastName, PSpecialty
FROM #CalendarTempTable
ORDER BY EventDate DESC
END
Thanks.
Edited by - bgam on 01/07/2002 10:23:08 AM
January 7, 2002 at 10:48 am
I see this in your sproc:
quote:
SELECT @ret "TransDutyCode"
That's probably why you are getting the return code... don't see a reason for it at a quick glance.
Also, from a performance standpoint, it looks like you may be able to use a CASE statement here to eliminate the temp table and the cursors.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 7, 2002 at 11:13 am
quote:
the ultimate result set I want doesn't seem to be returned to my application, but rather the select for the code is returned.
quote:
SELECT @ret "TransDutyCode"
In ado, this will be returned as the first recordset. If you need both values in your application, use nextrecordset to get to your final select.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply