Stored Procedure parameter in a stored procedure

Question

Below I have a query that I am working on that will create a stored procedure.

USE [PERFORMANCE]
GO

/****** Object:  StoredProcedure [dbo].[proc_performance_employee_manager_relation]    ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[proc_performance_employee_manager_relation] 
-- Add the parameters for the stored procedure here
    @beginDate datetime,
    @endDate datetime

AS
BEGIN

DECLARE @PERFORMANCE TABLE 
(
AGT_RESPONSIBLE INT,
EMPLOYEE_LAST_NAME VARCHAR(50),
EMPLOYEE_FIRST_NAME VARCHAR(50),
#_OF_COMPLIMENTS INT,
DEBIT_COUNT INT,
DEBIT_POTENTIAL_AMOUNT INT,
DEBIT_TNT_AMT INT,
#_OF_VALUE_ADDS INT,
DOLLAR_AMT_OF_VALUE_ADDS INT
)

INSERT INTO @PERFORMANCE
Exec proc_performance_AGT_roi '2015-05-01','2015-05-30'

SELECT
P.AGT_RESPONSIBLE, 
P.EMPLOYEE_LAST_NAME ,
P.EMPLOYEE_FIRST_NAME ,
P.#_OF_COMPLIMENTS ,
P.DEBIT_COUNT ,
P.DEBIT_POTENTIAL_AMOUNT ,
P.DEBIT_TNT_AMT ,
P.#_OF_VALUE_ADDS, 
P.DOLLAR_AMT_OF_VALUE_ADDS,
AGENT.EMPLOYEE_ID,
AGENT.FIRST_NAME,
AGENT.LAST_NAME,
AGENT.INTERNAL_TITLE,
AGENT.BRANCH_CODE,
AGENT.LOCATION,
MANAGER.EMPLOYEE_ID,
MANAGER.FIRST_NAME,
MANAGER.LAST_NAME,
MANAGER.INTERNAL_TITLE,
MANAGER.BRANCH_CODE,
MANAGER.LOCATION


FROM 
@PERFORMANCE P
LEFT JOIN omadb08.TANDT_EMPLOYEE.dbo.ADP_EMPLOYEE AGENT
    LEFT JOIN omadb08.TANDT_EMPLOYEE.dbo.ADP_EMPLOYEE MANAGER
    ON AGENT.REPORTS_TO_EMPLOYEE_ID = MANAGER.EMPLOYEE_ID
ON P.AGT_RESPONSIBLE = AGENT.EMPLOYEE_ID


END


GO

The code works fine, but my main concern is in the code where I have parameters:

INSERT INTO @PERFORMANCE
Exec proc_performance_AGT_roi '2015-05-01','2015-05-30'

The above is an insert from another procedure. My concern is that if I were to run this in a month, it will use these dates as a parameter and not ones that I will want. So is there a way that if I were to put the parameters in the overall procedure, it can copy what I inputed into that exec statement so the procedure will work, or is that just a fantasy?

Answer 1

I am not sure if this answers your question. If what you are asking is - can you use the input parameters for a stored procedure as input parameters to another, then yes, you can.

CREATE PROCEDURE [dbo].[proc_performance_employee_manager_relation] 
-- Add the parameters for the stored procedure here
    @beginDate datetime,
    @endDate datetime
AS
BEGIN
   ...
    INSERT INTO @PERFORMANCE
    Exec proc_performance_AGT_roi @beginDate ,@endDate 
   ...
END

The stored procedure proc_performance_employee_manager_relation can be executed elsewhere with any parameters you desire.

DECLARE @StartDate DATETIME
DECLARE @StartDate DATETIME

SET @StartDate='01/01/2015'
SET @EndDate='06/01/205'

EXEC proc_performance_employee_manager_relation @StartDate, @EndDate

Also, the locale in which the server is configured may be different than the way you are use to entering the date. Look at some examples on this page. You can force a specific conversion using functions such as:

-- SQL convert date string to datetime - time set to 00:00:00.000 or 12:00AM    
PRINT CONVERT(datetime,'07-10-2012',110)        -- Jul 10 2012 12:00AM    
PRINT CONVERT(datetime,'2012/07/10',111)        -- Jul 10 2012 12:00AM
PRINT CONVERT(datetime,'20120710',  112)        -- Jul 10 2012 12:00AM   

Answer 2

You need in parameters. Here is a stored procedure with an in parameter:

CREATE PROCEDURE uspGetAddress @City nvarchar(30)
AS
SELECT * 
FROM AdventureWorks.Person.Address
WHERE City = @City
GO

and this is how you call it:

EXEC uspGetAddress @City = 'New York'

You just need to modify your stored procedure to expect in parameters and call it with the necessary parameters when you call it.