Stored Procedure returning an int

Question

Hi i have the following stored proc in SQL Server 2005:

ALTER PROCEDURE [dbo].[slot_sp_EngineerTimeslots_Group]
    @PROPREF        VARCHAR(50),    
    @PRIORITYCODE   VARCHAR(3),
    @JOBLENGTH      INT = 0,
    @TIMESLOT       VARCHAR(3)  

AS

SET NOCOUNT ON

    DECLARE @TOTALDAYS          INT
    DECLARE @TOTALDAYSTARGET    INT
    DECLARE @COUNTER            INT
    DECLARE @STARTTIME          DATETIME
    DECLARE @MIDDAYTIME         DATETIME
    DECLARE @ENDTIME            DATETIME
    DECLARE @STARTDATE          DATETIME
    DECLARE @iSTARTDATE         DATETIME
    DECLARE @CONTRACT           VARCHAR(10)

    SET @iSTARTDATE = GETDATE()
    SET @STARTDATE = CONVERT(DATETIME,CONVERT(VARCHAR(10),@iSTARTDATE,120) + ' 00:00:00',120)

    SELECT @CONTRACT = CONTRACT FROM [tbl_property] WHERE [PROPREF] = @PROPREF

-- Get the contract Start/MidDay/End times
Select 
    @STARTTIME = CONVERT(VARCHAR(10), @STARTDATE, 120) + ' ' + CONVERT(VARCHAR(5), ContractStartTime, 108),
    @MIDDAYTIME = CONVERT(VARCHAR(10), @STARTDATE, 120) + ' ' + CONVERT(VARCHAR(5), ContractMiddayTime, 108), 
    @ENDTIME = CONVERT(VARCHAR(10), @STARTDATE, 120) + ' ' + CONVERT(VARCHAR(5), ContractEndTime, 108) 
From 
    [tbl_contract]
WHERE 
    [tbl_contract].[CONTRACT]=@CONTRACT     

-- Get Priority Times
Select 
    @TOTALDAYS =
        CASE 
            WHEN ROUND(NEARCOMPLETEDURATION/24,0) < NEARCOMPLETEDURATION/24 THEN ROUND(NEARCOMPLETEDURATION/24,0) + 1
            ELSE ROUND(NEARCOMPLETEDURATION/24,0) 
        END,
    @TOTALDAYSTARGET =
        CASE 
            WHEN ROUND(COMPLETEDURATION/24,0) < COMPLETEDURATION/24 THEN ROUND(COMPLETEDURATION/24,0) + 1
            ELSE ROUND(COMPLETEDURATION/24,0)  
        END 
FROM [ltbl_order_priority]
WHERE
    [ltbl_order_priority].[CONTRACT] = @CONTRACT
AND [ltbl_order_priority].[PRIORITYCODE] = @PRIORITYCODE

-- Not sure what this is for yet butits going to be fun!
SET @COUNTER = 0

BEGIN

--Create Temp Table

CREATE TABLE 
    #TempEngineer 
        (
            TempEngineer varchar(30), 
            BookedDate DateTime, 
            BookedFromTime DateTime, 
            BookedToTime DateTime, 
            TotalDays INT,  
            JobMins INT, 
            MinPMTime DateTime, 
            BTime DATETIME
        )

While (@COUNTER <= @TOTALDAYS)
    Begin
--Get details of active engineers from engineer table

    INSERT #TempEngineer 
            SELECT   
                    dbo.tbl_engineer.ENGINEER AS Engineer, 
                    @STARTDATE AS StartDate, 
                    CASE
                        WHEN ISNULL(dbo.tbl_engineer.STARTTIME,'') = '' THEN CONVERT(VARCHAR(10), @STARTDATE, 120) + ' ' + CONVERT(VARCHAR(5), @STARTTIME, 108) 
                        ELSE CONVERT(VARCHAR(10), @STARTDATE, 120) + ' ' + CONVERT(VARCHAR(5), dbo.tbl_engineer.STARTTIME, 108)
                    END AS StartTime,
                    CASE
                        WHEN ISNULL( dbo.tbl_engineer.ENDTIME,'') = '' THEN CONVERT(VARCHAR(10), @STARTDATE, 120) + ' ' + CONVERT(VARCHAR(5), @ENDTIME, 108)
                        ELSE CONVERT(VARCHAR(10), @STARTDATE, 120) + ' ' + CONVERT(VARCHAR(5), dbo.tbl_engineer.ENDTIME, 108)
                    END AS EndTime, 
                    @TOTALDAYS AS TotalDays, 
                    NULL AS JobMins,
                    NULL AS MinPMTime,
                    NULL AS BTime
            FROM    dbo.tbl_engineer 
            WHERE   dbo.tbl_engineer.ACTIVE = 1
            AND     Engineer NOT IN (SELECT Engineer    
                    FROM  slot_tbl_Schedule  
                    WHERE      bookedDate <= DATEADD(DAY, @TOTALDAYS, @STARTDATE)
                    AND        bookedDate >= @STARTDATE)


--Get Details of Free time form schedule table              
    INSERT #TempEngineer 
            SELECT     
                    slot_tbl_Schedule.Engineer AS Engineer, 
                    slot_tbl_Schedule.BookedDate AS BookDate,
                    slot_tbl_Schedule.FromTime AS FromTime, 
                    ISNULL(slot_tbl_Schedule.ToTime, CONVERT(VARCHAR(10), @STARTDATE, 120) + ' ' + CONVERT(VARCHAR(5), ENDTIME, 108)) AS ToTime, 
                    @TOTALDAYS AS TotalDays, 
                    NULL AS JobMins,
                    NULL AS MinPMTime,
                    NULL AS BTime

            FROM       
                    (
                        SELECT     
                                Engineer, 
                                BookedDate, 
                                ToTime AS FromTime,
                                (SELECT     MIN(fromTime) FROM slot_tbl_Schedule x WHERE x.engineer = s1.engineer AND x.bookedDate = s1.bookedDate AND x.fromTime >= s1.ToTime) AS ToTime 
                        FROM    slot_tbl_Schedule s1 
                        WHERE   CONVERT(DATETIME,CONVERT(VARCHAR(10),bookedDate,120),120) =  @STARTDATE 

                        UNION ALL 

                        SELECT     e2.Engineer, s2.BookedDate,  CONVERT(VARCHAR(10), @STARTDATE, 120) + ' ' + CONVERT(VARCHAR(5), e2.StartTime, 114) AS FromTime, MIN(s2.FromTime) AS ToTime 
                        FROM       tbl_engineer e2 INNER JOIN slot_tbl_Schedule s2 ON s2.engineer = e2.engineer                     
                        WHERE      CONVERT(DATETIME,CONVERT(VARCHAR(10),s2.bookedDate,120),120) =  @STARTDATE   
                        GROUP BY   e2.Engineer, s2.BookedDate, e2.StartTime 
                        HAVING     MIN(s2.FromTime) > e2.StartTime
                    )

                slot_tbl_Schedule INNER JOIN dbo.tbl_engineer engineer ON engineer.ENGINEER = slot_tbl_Schedule.Engineer 

        Select @COUNTER = @COUNTER + 1
        Select @STARTDATE = DATEADD(Day, 1, @STARTDATE)


    END -- While End

END -- Temp Table End

--Data from above select statements which is in the temp table  
--
CREATE TABLE 
    #TempEngineerGroup 
        (
            AppointmentType VARCHAR(50),
            BookedDate DateTime, 
            BookedFromTime DateTime, 
            BookedToTime DateTime, 
            TotalDays INT,  
            JobMins INT
        )

IF @TIMESLOT = 'AM'
            INSERT INTO [#TempEngineerGroup] 
            SELECT  'FreeTime' AS [AppointmentType], BookedDate, BookedFromTime, BookedToTime, TotalDays, DATEDIFF(mi,BookedFromTime, BookedToTime) AS [JobMins]
            FROM #TempEngineer 
            WHERE (DATEDIFF(mi,BookedFromTime, BookedToTime) >= @JOBLENGTH) 
                AND (DATEDIFF(mi,BookedFromTime, BookedToTime) < 600) 
                AND BookedFromTime < CONVERT(DATETIME,CONVERT(VARCHAR(10), BookedFromTime, 120) + ' ' + CONVERT(VARCHAR(5), @MIDDAYTIME, 114),120)

            UNION

            SELECT [slot_tbl_Schedule].[AppointmentType],[slot_tbl_Schedule].[BookedDate],[slot_tbl_Schedule].[FromTime],[slot_tbl_Schedule].[ToTime],0,[slot_tbl_Schedule].[JobMins]
            FROM [slot_tbl_Schedule]
            INNER JOIN [#TempEngineer] ON [slot_tbl_Schedule].[Engineer] = [#TempEngineer].[TempEngineer]
            WHERE [slot_tbl_Schedule].[BookedDate] BETWEEN @iSTARTDATE AND DATEADD(dd,@TOTALDAYS,@iSTARTDATE)
            ORDER BY BookedFromTime

IF @TIMESLOT = 'AT'
            INSERT INTO [#TempEngineerGroup] 
            SELECT 'FreeTime' AS [AppointmentType], BookedDate, BookedFromTime, BookedToTime, TotalDays, DATEDIFF(mi,BookedFromTime, BookedToTime) AS JobMins 
            FROM #TempEngineer 
            WHERE (DATEDIFF(mi,BookedFromTime, BookedToTime) >= @JOBLENGTH) 
                AND (DATEDIFF(mi,BookedFromTime, BookedToTime) < 600) 

            UNION

            SELECT [slot_tbl_Schedule].[AppointmentType], [slot_tbl_Schedule].[BookedDate],[slot_tbl_Schedule].[FromTime],[slot_tbl_Schedule].[ToTime],0,[slot_tbl_Schedule].[JobMins]
            FROM [slot_tbl_Schedule]
            INNER JOIN [#TempEngineer] ON [slot_tbl_Schedule].[Engineer] = [#TempEngineer].[TempEngineer]
            WHERE [slot_tbl_Schedule].[BookedDate] BETWEEN @iSTARTDATE AND DATEADD(dd,@TOTALDAYS,@iSTARTDATE)
            ORDER BY BookedFromTime

IF @TIMESLOT = 'PM'
            INSERT INTO [#TempEngineerGroup] 
            SELECT 'FreeTime' AS [AppointmentType], BookedDate, BookedFromTime, BookedToTime, TotalDays, DATEDIFF(mi,BookedFromTime, BookedToTime) AS JobMins
            FROM (
                    SELECT TempEngineer, BookedDate, BookedFromTime AS BTime, BookedToTime, TotalDays, JobMins, MinPMTime,
                            CASE
                                WHEN BookedFromTime >=  CONVERT(DATETIME,CONVERT(VARCHAR(10), BookedFromTime, 120) + ' ' + CONVERT(VARCHAR(5), @MIDDAYTIME, 114),120) Then BookedFromTime
                                WHEN BookedFromTime <  CONVERT(DATETIME,CONVERT(VARCHAR(10), BookedFromTime, 120) + ' ' + CONVERT(VARCHAR(5), @MIDDAYTIME, 114),120) Then MinPMTime
                            END AS BookedFromTime
                    FROM (
                            SELECT  TempEngineer, BookedDate, BookedFromTime, BookedToTime, TotalDays, DATEDIFF(mi,BookedFromTime, BookedToTime) AS JobMins,
                                    max(CASE WHEN BookedFromTime < CONVERT(DATETIME,CONVERT(VARCHAR(10), BookedFromTime, 120) + ' ' + CONVERT(VARCHAR(5), @MIDDAYTIME, 114),120) AND IsNull(PMAppointments, 0) >= 0 THEN CONVERT(DATETIME,CONVERT(VARCHAR(10), BookedFromTime, 120) + ' ' + CONVERT(VARCHAR(5), @MIDDAYTIME, 114),120)
                                             WHEN BookedFromTime > CONVERT(DATETIME,CONVERT(VARCHAR(10), BookedFromTime, 120) + ' ' + CONVERT(VARCHAR(5), @MIDDAYTIME, 114),120) AND IsNull(PMAppointments, 0) >= 0 THEN BookedFromTime END) AS MinPMTime
                            FROM (  
                                    SELECT TempEngineer, BookedDate, BookedFromTime, BookedToTime, TotalDays, DATEDIFF(mi,BookedFromTime, BookedToTime) AS JobMins,
                                            CASE  
                                                WHEN convert(datetime,convert(varchar(5),BookedFromTime,108),120) >= convert(datetime,convert(varchar(5),@ENDTIME,108),120) THEN DATEDIFF(mi,convert(datetime,convert(varchar(5),BookedFromTime,108),120), convert(datetime,convert(varchar(5),@ENDTIME,108),120))/@JOBLENGTH
                                                WHEN convert(datetime,convert(varchar(5),BookedToTime,108),120) >= convert(datetime,convert(varchar(5),@MIDDAYTIME,108),120) THEN DATEDIFF(mi,convert(datetime,convert(varchar(5),@MIDDAYTIME,108),120), convert(datetime,convert(varchar(5),BookedToTime,108),120))/@JOBLENGTH                                 
                                            END As PMAppointments                
                                    FROM #TempEngineer  

                                )INSIDEQUERY_A
                                GROUP BY TempEngineer, BookedDate, BookedFromTime, BookedToTime, TotalDays, JobMins
                        )INSIDEQUERY_B              
                        GROUP BY TempEngineer, BookedDate, BookedFromTime, BookedToTime, TotalDays, JobMins, MinPMTime
                )OUTSIDEQUERY       
                WHERE (DATEDIFF(mi,BookedFromTime, BookedToTime) >= @JOBLENGTH) 
                        AND (DATEDIFF(mi,BookedFromTime, BookedToTime) < 600)
                GROUP BY TempEngineer, BookedDate, BookedFromTime, BookedToTime, TotalDays, JobMins, MinPMTime

            UNION

            SELECT [slot_tbl_Schedule].[AppointmentType], [slot_tbl_Schedule].[BookedDate],[slot_tbl_Schedule].[FromTime],[slot_tbl_Schedule].[ToTime],0,[slot_tbl_Schedule].[JobMins]
            FROM [slot_tbl_Schedule]
            INNER JOIN [#TempEngineer] ON [slot_tbl_Schedule].[Engineer] = [#TempEngineer].[TempEngineer]
            WHERE [slot_tbl_Schedule].[BookedDate] BETWEEN @iSTARTDATE AND DATEADD(dd,@TOTALDAYS,@iSTARTDATE)
            ORDER BY BookedFromTime


DECLARE @C1_AppointmentType VARCHAR(50)
DECLARE @C1_BookedDate DateTime
DECLARE @C1_BookedFromTime DateTime
DECLARE @C1_BookedToTime DateTime
DECLARE @C1_TotalDays INT
DECLARE @C1_JobMins INT

DECLARE @WC_AppointmentType VARCHAR(50)
DECLARE @WC_BookedDate DateTime
DECLARE @WC_BookedFromTime DateTime
DECLARE @WC_BookedToTime DateTime
DECLARE @WC_TotalDays INT
DECLARE @WC_JobMins INT

DECLARE @Saved AS BIT

CREATE TABLE 
    #TempEngineerGroupMain 
        (
            AppointmentType VARCHAR(50),
            BookedDate DateTime, 
            BookedFromTime DateTime, 
            BookedToTime DateTime, 
            TotalDays INT,  
            JobMins INT
        )


DECLARE c1 CURSOR READ_ONLY
FOR 
    SELECT *
    FROM [#TempEngineerGroup]
    ORDER BY  [BookedDate] ASC, [AppointmentType] ASC, BookedFromTime ASC, [JobMins] desc

OPEN c1
    FETCH NEXT FROM c1 INTO @C1_AppointmentType, @C1_BookedDate, @C1_BookedFromTime, @C1_BookedToTime, @C1_TotalDays, @C1_JobMins

SET @Saved = 0

WHILE @@FETCH_STATUS = 0
BEGIN
    IF ISNULL(@WC_AppointmentType,'') = ''
    BEGIN
        SET @WC_AppointmentType = @C1_AppointmentType
        SET @WC_BookedDate = @C1_BookedDate
        SET @WC_BookedFromTime = @C1_BookedFromTime
        SET @WC_BookedToTime = @C1_BookedToTime
        SET @WC_TotalDays = @C1_TotalDays
        SET @WC_JobMins = @C1_JobMins
    END
    ELSE -- Start Checks
        BEGIN
            IF @C1_BookedDate != @WC_BookedDate -- Different Date
                BEGIN                   
                    INSERT INTO [#TempEngineerGroupMain] ([AppointmentType],[BookedDate],[BookedFromTime],[BookedToTime],[TotalDays],[JobMins])
                        VALUES ( @WC_AppointmentType, @WC_BookedDate, @WC_BookedFromTime, @WC_BookedToTime, @WC_TotalDays, DATEDIFF(mi,@WC_BookedFromTime,@WC_BookedToTime))
                    SET @WC_AppointmentType = @C1_AppointmentType
                    SET @WC_BookedDate = @C1_BookedDate
                    SET @WC_BookedFromTime = @C1_BookedFromTime
                    SET @WC_BookedToTime = @C1_BookedToTime
                    SET @WC_TotalDays = @C1_TotalDays
                    SET @WC_JobMins = @C1_JobMins
                    SET @Saved = 0
                END
            IF @C1_AppointmentType != @WC_AppointmentType -- Different Appointment so Store Value
                BEGIN   
                    INSERT INTO [#TempEngineerGroupMain] ([AppointmentType],[BookedDate],[BookedFromTime],[BookedToTime],[TotalDays],[JobMins])
                        VALUES ( @WC_AppointmentType, @WC_BookedDate, @WC_BookedFromTime, @WC_BookedToTime, @WC_TotalDays, DATEDIFF(mi,@WC_BookedFromTime,@WC_BookedToTime))
                    SET @WC_AppointmentType = @C1_AppointmentType
                    SET @WC_BookedDate = @C1_BookedDate
                    SET @WC_BookedFromTime = @C1_BookedFromTime
                    SET @WC_BookedToTime = @C1_BookedToTime
                    SET @WC_TotalDays = @C1_TotalDays
                    SET @WC_JobMins = @C1_JobMins
                    SET @Saved = 0
                END
            ELSE IF @C1_BookedDate = @WC_BookedDate AND @C1_AppointmentType = @WC_AppointmentType AND @C1_BookedFromTime <= @WC_BookedFromTime AND @C1_BookedToTime >= @WC_BookedFromTime 
                BEGIN -- Change Start Time
                    SET @WC_BookedFromTime = @C1_BookedFromTime
                    SET @Saved = 0
                END
            ELSE IF @C1_BookedDate = @WC_BookedDate AND @C1_AppointmentType = @WC_AppointmentType AND @C1_BookedFromTime <= @WC_BookedToTime AND @C1_BookedToTime >= @WC_BookedFromTime 
                BEGIN -- Change End Time
                    SET @WC_BookedToTime = @C1_BookedToTime
                    SET @Saved = 0
                END
            ELSE IF @C1_BookedDate = @WC_BookedDate AND @C1_AppointmentType = @WC_AppointmentType AND @C1_BookedFromTime < @WC_BookedFromTime AND @C1_BookedToTime < @WC_BookedFromTime
                BEGIN -- New
                    INSERT INTO [#TempEngineerGroupMain] ([AppointmentType],[BookedDate],[BookedFromTime],[BookedToTime],[TotalDays],[JobMins])
                        VALUES ( @WC_AppointmentType, @WC_BookedDate, @WC_BookedFromTime, @WC_BookedToTime, @WC_TotalDays, DATEDIFF(mi,@WC_BookedFromTime,@WC_BookedToTime))
                    SET @WC_AppointmentType = @C1_AppointmentType
                    SET @WC_BookedDate = @C1_BookedDate
                    SET @WC_BookedFromTime = @C1_BookedFromTime
                    SET @WC_BookedToTime = @C1_BookedToTime
                    SET @WC_TotalDays = @C1_TotalDays
                    SET @WC_JobMins = @C1_JobMins
                    SET @Saved = 1
                END
            ELSE IF @C1_BookedDate = @WC_BookedDate AND @C1_AppointmentType = @WC_AppointmentType AND @C1_BookedFromTime > @WC_BookedToTime 
                BEGIN -- New
                    INSERT INTO [#TempEngineerGroupMain] ([AppointmentType],[BookedDate],[BookedFromTime],[BookedToTime],[TotalDays],[JobMins])
                        VALUES ( @WC_AppointmentType, @WC_BookedDate, @WC_BookedFromTime, @WC_BookedToTime, @WC_TotalDays, DATEDIFF(mi,@WC_BookedFromTime,@WC_BookedToTime))
                    SET @WC_AppointmentType = @C1_AppointmentType
                    SET @WC_BookedDate = @C1_BookedDate
                    SET @WC_BookedFromTime = @C1_BookedFromTime
                    SET @WC_BookedToTime = @C1_BookedToTime
                    SET @WC_TotalDays = @C1_TotalDays
                    SET @WC_JobMins = @C1_JobMins
                    SET @Saved = 1
                END
        END
    FETCH NEXT FROM c1 INTO @C1_AppointmentType, @C1_BookedDate, @C1_BookedFromTime, @C1_BookedToTime, @C1_TotalDays, @C1_JobMins
END 

IF @Saved = 0 
    INSERT INTO [#TempEngineerGroupMain] ([AppointmentType],[BookedDate],[BookedFromTime],[BookedToTime],[TotalDays],[JobMins])
    VALUES ( @WC_AppointmentType, @WC_BookedDate, @WC_BookedFromTime, @WC_BookedToTime, @WC_TotalDays, DATEDIFF(mi,@WC_BookedFromTime,@WC_BookedToTime))

CLOSE c1
DEALLOCATE c1

SELECT *
FROM [#TempEngineerGroupMain]

thr problem is that while it is supposed to return rows of from the temp table in linq it returns an int is there any reason as to why this would happen?

Marc Gravell · Accepted Answer · 2009-06-15 08:00:06Z

One common reason is that stored procedures have notoriously weak metadata, particularly for complex code with branches etc. It is very hard for the external code to really understand what is being selected/returned.

A common trick is to replace the sproc with something mind-numbingly simple (i.e. that simply returns any sample data of the desired schema) for the process of inspection, and then swap back to the real code. Alternatively, if possible consider swapping to a UDF - as UDFs have much stronger schema metadata.

Of course, you could also hand-edit the DBML based on something similar that works correctly.

As an aside - you may have a number of issues with that existing code, including (but not limited to) DML/DDL transitions (forces recompile), multiple updates to the same table (forces selective recompile) - not to mention the cursor usage (which may of may not be necessary). Table variables (@table rather than #table) may be useful too. I'm not offering to re-write it; just note some things you might want to look at.

IDisposable · Accepted Answer · 2009-08-10 03:32:25Z

-1

The first SELECT in your SPROC is yielding an INT into the temporary variable

SELECT @CONTRACT = CONTRACT FROM [tbl_property] WHERE [PROPREF] = @PROPREF

which is what is tripping up the metadata. You should do a

 SET @CONTRACT = (SELECT CONTRACT FROM [tbl_property] WHERE [PROPREF] = @PROPREF)

instead.

answered Aug 10, 2009 at 3:32

IDisposable

1,86822 silver badges22 bronze badges

Collectives™ on Stack Overflow

Stored Procedure returning an int

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related