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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy