asked on

How to return the value of the integer variables used in the stored procedure

I had this question after viewing Finding records between to times and spanning over midnight.

When designing the Crystal Report that used the stored procedure I found that using the integer variables that defined the start and end times of the shift would extremely useful to display dynamic labels. So how would I be able to incorporate this into the stored procedure and return values such as:

SET @8hrfirstshiftstart = 7 (Return 0700 - 1500)
SET @8hrfirstshiftend = 15

(Calculate and return 1500 - 2300)

SET @8hrthirdshiftstart = 23 (Return 2300 - 0700)
SET @8hrthirdshiftend = 7

SET @12hrfirstshiftstart = 7 (Return 0700 - 1900)
SET @12hrfirstshiftend = 19
(Calculate and return 1900 -0700)

Here is the stored procedure:

USE [CT_PSIMS]
GO
/****** Object:  StoredProcedure [dbo].[CR_911_Calls_ByShiftAndCount]    Script Date: 11/22/2016 09:46:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Name
-- Create date: 
-- Description:	Find all 911 calls in E911Data per shift
-- =============================================
ALTER PROCEDURE [dbo].[CR_911_Calls_ByShiftAndCount] 
	-- Add the parameters for the stored procedure here
	@Agency int = 0, 
	@Year int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @8hrfirstshiftstart as int  -- Don't need second shift values
	DECLARE @8hrfirstshiftend as int
	DECLARE @8hrthirdshiftstart as int
	DECLARE @8hrthirdshiftend as int

	DECLARE @12hrfirstshiftstart as int  -- Don't need second shift values
	DECLARE @12hrfirstshiftend as int

	SET @8hrfirstshiftstart = 7
	SET @8hrfirstshiftend = 15
	SET @8hrthirdshiftstart = 23
	SET @8hrthirdshiftend = 7
	SET @12hrfirstshiftstart = 7
	SET @12hrfirstshiftend = 19



    -- Insert statements for procedure here
SELECT
      CurYRshift_1_8hr
    , CurYRshift_2_8hr
    , CurYRshift_3_8hr
    , PreYRshift_1_8hr
    , PreYRshift_2_8hr
    , PreYRshift_3_8hr
    , CurYRshift_1_8hr - PreYRshift_1_8hr AS DiffShift_1_8hr
    , CurYRshift_2_8hr - PreYRshift_2_8hr AS DiffShift_2_8hr
    , CurYRshift_3_8hr - PreYRshift_3_8hr AS DiffShift_3_8hr
    , CurYRshift_1_12Hr
    , CurYRshift_2_12Hr
    , PreYRshift_1_12Hr
    , PreYRshift_2_12Hr
    , CurYRshift_1_12Hr - PreYRshift_1_12Hr AS DiffShift_1_12hr
    , CurYRshift_2_12Hr - PreYRshift_2_12Hr AS DiffShift_2_12hr
FROM (
    SELECT
          COUNT(CASE WHEN EntryYear = 1 AND hr8_shift_no = 1 THEN 1 END) AS CurYRshift_1_8hr
        , COUNT(CASE WHEN EntryYear = 1 AND hr8_shift_no = 2 THEN 1 END) AS CurYRshift_2_8hr
        , COUNT(CASE WHEN EntryYear = 1 AND hr8_shift_no = 3 THEN 1 END) AS CurYRshift_3_8hr
        , COUNT(CASE WHEN EntryYear = 0 AND hr8_shift_no = 1 THEN 1 END) AS PreYRshift_1_8hr
        , COUNT(CASE WHEN EntryYear = 0 AND hr8_shift_no = 2 THEN 1 END) AS PreYRshift_2_8hr
        , COUNT(CASE WHEN EntryYear = 0 AND hr8_shift_no = 3 THEN 1 END) AS PreYRshift_3_8hr
        , COUNT(CASE WHEN EntryYear = 1 AND hr12_shift_no = 1 THEN 1 END) AS CurYRshift_1_12Hr
        , COUNT(CASE WHEN EntryYear = 1 AND hr12_shift_no = 2 THEN 1 END) AS CurYRshift_2_12Hr
        , COUNT(CASE WHEN EntryYear = 0 AND hr12_shift_no = 1 THEN 1 END) AS PreYRshift_1_12Hr
        , COUNT(CASE WHEN EntryYear = 0 AND hr12_shift_no = 2 THEN 1 END) AS PreYRshift_2_12Hr
		
    FROM (
            select
                  EntryYear
                , case
                    when EntryHour  < @8hrthirdshiftend  or EntryHour > @8hrthirdshiftstart then 3
                    when EntryHour >= @8hrfirstshiftstart and EntryHour < @8hrfirstshiftend then 1
                    else 2
                  end hr8_shift_no
                , case
                    when EntryHour >= @12hrfirstshiftstart and EntryHour < @12hrfirstshiftend then 1
                    else 2
                  end hr12_shift_no
            from e911data
            cross apply (select
                             datepart(hour,[EntryDateTime]) as EntryHour 
                           , datepart(year,[EntryDateTime]) - (@year-1) as EntryYear
                        ) ca
            where EntryDateTime >= dateadd(year,(@year-1901),'19000101') /* 1 Jan year before @ year e.g. 2015 */
              and EntryDateTime <  dateadd(year,(@year-1899),'19000101')  /* 1 Jan year after @ year  e.g. 2017 */
			  and Agency = @Agency
          ) AS derived
    ) AS d	
END

Open in new window

SOLUTION