Solved

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

Posted on 2016-11-22
3
31 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 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 250 total points
Comment Utility
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 17

Accepted Solution

by:
Pawan Kumar Khowal earned 250 total points
Comment Utility
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
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now