[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2016-11-22
3
Medium Priority
?
72 Views
Last Modified: 2016-11-25
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

0
Comment
Question by:MikeM670
3 Comments
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1000 total points
ID: 41898054
Simply incorporate those variables into your SELECT statement.

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
    , @8hrfirstshiftstart AS 8hrfirstshiftstart
    , @8hrfirstshiftend = AS 8hrfirstshiftend
    , @8hrthirdshiftstart = AS 8hrthirdshiftstart
    , @8hrthirdshiftend = AS 8hrthirdshiftend
    , @12hrfirstshiftstart = AS 12hrfirstshiftstart
    , @12hrfirstshiftend = AS 12hrfirstshiftend
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

0
 
LVL 37

Accepted Solution

by:
Pawan Kumar earned 1000 total points
ID: 41898279
try...

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
	, @8hrfirstshiftstart [8hrfirstshiftstart]
    , @8hrfirstshiftend  [8hrfirstshiftend]
    , @8hrthirdshiftstart [8hrthirdshiftstart]
    , @8hrthirdshiftend [8hrthirdshiftend]
    , @12hrfirstshiftstart [12hrfirstshiftstart]
    , @12hrfirstshiftend [12hrfirstshiftend]
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

0
 

Author Closing Comment

by:MikeM670
ID: 41901679
Thanks to both of you for the help.  I split the points between both of you.  One for the original solution the other for the example.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question