0

I'm having a problem rectifying issue of stored procedure with no input parameters, failing in a SQL Agent job stating, expecting input parameter.

Any ideas or simple solutions ! lol

Here's the Agent Job call: 

    Exec e3.dbo.storedprocedure_name

Here's the stored procedure:

    CREATE PROCEDURE [dbo].[storedprocedure_name]
        @EmpNo NCHAR(10) Output,
        @EmpLN NCHAR(50) Output,
        @EmpFN NCHAR(50) Output,
        @FOB BIT Output,
        @SecAcc BIT Output,
        @Network BIT Output,
        @VPN BIT Output,
        @Email BIT Output,
        @Printer BIT Output,
        @Fuel BIT Output,
        @BSTC BIT Output,
        @Accounts BIT Output,
        @Telephone BIT Output,
        @Alarm BIT Output,
        @EZPass1 BIT Output,
        @EZPass2 BIT Output,
        @EmpReqID INT Output,
        @ReqDate DATETIME Output
    AS
    BEGIN
        SET NOCOUNT ON;

    -- Check FOB Change Request
    If Exists 
    (
    SELECT ERA.Emp_LastName AS [@EmpLN], ERA.Emp_FirstName AS [@EmpFN], ERA.Emp_DtAudStep_Complete AS [@ReqDate], ERA.Emp_ReqID as [@EmpReqID]
    FROM   [e3].[dbo].E3_Emp_ReqAudit AS ERA INNER JOIN
           [e3].[dbo].E3_Emp_Access   AS EA ON ERA.Emp_AB_No = EA.EA_AB_No INNER JOIN
           [e3].[dbo].E3_Employee     AS E  ON ERA.Emp_AB_No = E.Emp_AB_No
    WHERE (ERA.Emp_Req_Action = 'E1') AND (ERA.Emp_AudStep_Completed = '5') AND (ERA.Emp_Contractor = 'False') 
       OR (ERA.Emp_Req_Action = 'E1') AND (ERA.Emp_AudStep_Completed = '2') AND (ERA.Emp_Contractor = 'True') 
      AND (ERA.Emp_DtAudStep_Complete =
                    (SELECT  MAX(Emp_DtAudStep_Complete) AS Expr1
                      FROM   [e3].[dbo].E3_Emp_ReqAudit AS E3_Emp_ReqAudit_1
                      WHERE (Emp_AB_No = EA.EA_AB_No) 
                        AND (Emp_Req_Action = 'E1') AND (Emp_AudStep_Completed = '5') AND (ERA.Emp_Contractor = 'False') 
                         OR (Emp_Req_Action = 'E1') AND (Emp_AudStep_Completed = '2') AND (ERA.Emp_Contractor = 'True'))) 
     AND ERA.Emp_DtAudStep_Complete <= dateadd(day,-1,GETDATE())
     AND (ERA.Emp_FOBadge           <> E.Emp_CA_Badge_No1) 
      OR (ERA.Emp_FOBadge           <> E.Emp_CA_Badge_No1)
     )
        SET @FOB    = 'True'
    ELSE
        SET @FOB    = 'False'

    -- Check Secondary Access 
    If Exists 
    (
    SELECT ERA.Emp_LastName AS [@EmpLN], ERA.Emp_FirstName AS [@EmpFN], ERA.Emp_DtAudStep_Complete AS [@ReqDate], ERA.Emp_ReqID as [@EmpReqID]
    FROM   [e3].[dbo].E3_Emp_ReqAudit AS ERA INNER JOIN
           [e3].[dbo].E3_Emp_Access   AS EA ON ERA.Emp_AB_No = EA.EA_AB_No INNER JOIN
           [e3].[dbo].E3_Employee     AS E  ON ERA.Emp_AB_No = E.Emp_AB_No
    WHERE (ERA.Emp_Req_Action = 'E1') AND (ERA.Emp_AudStep_Completed = '5') AND (ERA.Emp_Contractor = 'False') 
       OR (ERA.Emp_Req_Action = 'E1') AND (ERA.Emp_AudStep_Completed = '2') AND (ERA.Emp_Contractor = 'True') 
      AND (ERA.Emp_DtAudStep_Complete =
                    (SELECT  MAX(Emp_DtAudStep_Complete) AS Expr1
                      FROM   [e3].[dbo].E3_Emp_ReqAudit AS E3_Emp_ReqAudit_1
                      WHERE (Emp_AB_No = EA.EA_AB_No) 
                        AND (Emp_Req_Action = 'E1') AND (Emp_AudStep_Completed = '5') AND (ERA.Emp_Contractor = 'False') 
                         OR (Emp_Req_Action = 'E1') AND (Emp_AudStep_Completed = '2') AND (ERA.Emp_Contractor = 'True'))) 
     AND   ERA.Emp_DtAudStep_Complete       <= dateadd(day,-1,GETDATE())
     AND  (ERA.Emp_Sec_Access   = 'True') AND (EA.EA_Sec_CA     = 'False') 
     )
        SET @SecAcc = 'True'
    ELSE
        SET @SecAcc = 'False'

    -- Check Network Access 
    If Exists 
    (
    SELECT ERA.Emp_LastName AS [@EmpLN], ERA.Emp_FirstName AS [@EmpFN], ERA.Emp_DtAudStep_Complete AS [@ReqDate], ERA.Emp_ReqID as [@EmpReqID]
    FROM   [e3].[dbo].E3_Emp_ReqAudit AS ERA INNER JOIN
           [e3].[dbo].E3_Emp_Access   AS EA ON ERA.Emp_AB_No = EA.EA_AB_No INNER JOIN
           [e3].[dbo].E3_Employee     AS E  ON ERA.Emp_AB_No = E.Emp_AB_No
    WHERE (ERA.Emp_Req_Action = 'E1') AND (ERA.Emp_AudStep_Completed = '5') AND (ERA.Emp_Contractor = 'False') 
       OR (ERA.Emp_Req_Action = 'E1') AND (ERA.Emp_AudStep_Completed = '2') AND (ERA.Emp_Contractor = 'True') 
      AND (ERA.Emp_DtAudStep_Complete =
                    (SELECT  MAX(Emp_DtAudStep_Complete) AS Expr1
                      FROM   [e3].[dbo].E3_Emp_ReqAudit AS E3_Emp_ReqAudit_1
                      WHERE (Emp_AB_No = EA.EA_AB_No) 
                        AND (Emp_Req_Action = 'E1') AND (Emp_AudStep_Completed = '5') AND (ERA.Emp_Contractor = 'False') 
                         OR (Emp_Req_Action = 'E1') AND (Emp_AudStep_Completed = '2') AND (ERA.Emp_Contractor = 'True'))) 
     AND   ERA.Emp_DtAudStep_Complete       <= dateadd(day,-1,GETDATE())
     AND  (ERA.Emp_NetworkID   <> 'True') AND (EA.EA_Network        = 'False') 
     )
        SET @Network = 'True'
    ELSE
        SET @Network = 'False'

    -- Check VPN Access 
    If Exists 
    (
    SELECT ERA.Emp_LastName AS [@EmpLN], ERA.Emp_FirstName AS [@EmpFN], ERA.Emp_DtAudStep_Complete AS [@ReqDate], ERA.Emp_ReqID as [@EmpReqID]
    FROM   [e3].[dbo].E3_Emp_ReqAudit AS ERA INNER JOIN
           [e3].[dbo].E3_Emp_Access   AS EA ON ERA.Emp_AB_No = EA.EA_AB_No INNER JOIN
           [e3].[dbo].E3_Employee     AS E  ON ERA.Emp_AB_No = E.Emp_AB_No
    WHERE (ERA.Emp_Req_Action = 'E1') AND (ERA.Emp_AudStep_Completed = '5') AND (ERA.Emp_Contractor = 'False') 
       OR (ERA.Emp_Req_Action = 'E1') AND (ERA.Emp_AudStep_Completed = '2') AND (ERA.Emp_Contractor = 'True') 
      AND (ERA.Emp_DtAudStep_Complete =
                    (SELECT  MAX(Emp_DtAudStep_Complete) AS Expr1
                      FROM   [e3].[dbo].E3_Emp_ReqAudit AS E3_Emp_ReqAudit_1
                      WHERE (Emp_AB_No = EA.EA_AB_No) 
                        AND (Emp_Req_Action = 'E1') AND (Emp_AudStep_Completed = '5') AND (ERA.Emp_Contractor = 'False') 
                         OR (Emp_Req_Action = 'E1') AND (Emp_AudStep_Completed = '2') AND (ERA.Emp_Contractor = 'True'))) 
     AND   ERA.Emp_DtAudStep_Complete    <= dateadd(day,-1,GETDATE())
     AND  (ERA.Emp_VPN = 'True') 
     AND (EA.EA_VPN    = 'False') 
    )
        SET @VPN = 'True'
    ELSE
        SET @VPN = 'False'

    -- Check EMAIL Access 
    If Exists 
    (
    SELECT ERA.Emp_LastName AS [@EmpLN], ERA.Emp_FirstName AS [@EmpFN], ERA.Emp_DtAudStep_Complete AS [@ReqDate], ERA.Emp_ReqID as [@EmpReqID]
    FROM   [e3].[dbo].E3_Emp_ReqAudit AS ERA INNER JOIN
           [e3].[dbo].E3_Emp_Access   AS EA ON ERA.Emp_AB_No = EA.EA_AB_No INNER JOIN
           [e3].[dbo].E3_Employee     AS E  ON ERA.Emp_AB_No = E.Emp_AB_No
    WHERE (ERA.Emp_Req_Action = 'E1') AND (ERA.Emp_AudStep_Completed = '5') AND (ERA.Emp_Contractor = 'False') 
       OR (ERA.Emp_Req_Action = 'E1') AND (ERA.Emp_AudStep_Completed = '2') AND (ERA.Emp_Contractor = 'True') 
      AND (ERA.Emp_DtAudStep_Complete =
                    (SELECT  MAX(Emp_DtAudStep_Complete) AS Expr1
                      FROM   [e3].[dbo].E3_Emp_ReqAudit AS E3_Emp_ReqAudit_1
                      WHERE (Emp_AB_No = EA.EA_AB_No) 
                        AND (Emp_Req_Action = 'E1') AND (Emp_AudStep_Completed = '5') AND (ERA.Emp_Contractor = 'False') 
                         OR (Emp_Req_Action = 'E1') AND (Emp_AudStep_Completed = '2') AND (ERA.Emp_Contractor = 'True'))) 
     AND  ERA.Emp_DtAudStep_Complete     <= dateadd(day,-1,GETDATE())
     AND (ERA.Emp_Email = 'True') 
     AND (EA.EA_Email   = 'False') 
     )
        SET @Email = 'True'
    ELSE
        SET @Email = 'False'

    -- Check Local Printer Access 
    If Exists 
    (
    SELECT ERA.Emp_LastName AS [@EmpLN], ERA.Emp_FirstName AS [@EmpFN], ERA.Emp_DtAudStep_Complete AS [@ReqDate], ERA.Emp_ReqID as [@EmpReqID]
    FROM   [e3].[dbo].E3_Emp_ReqAudit AS ERA INNER JOIN
           [e3].[dbo].E3_Emp_Access   AS EA ON ERA.Emp_AB_No = EA.EA_AB_No INNER JOIN
           [e3].[dbo].E3_Employee     AS E  ON ERA.Emp_AB_No = E.Emp_AB_No
    WHERE (ERA.Emp_Req_Action = 'E1') AND (ERA.Emp_AudStep_Completed = '5') AND (ERA.Emp_Contractor = 'False') 
       OR (ERA.Emp_Req_Action = 'E1') AND (ERA.Emp_AudStep_Completed = '2') AND (ERA.Emp_Contractor = 'True') 
      AND (ERA.Emp_DtAudStep_Complete =
                    (SELECT  MAX(Emp_DtAudStep_Complete) AS Expr1
                      FROM   [e3].[dbo].E3_Emp_ReqAudit AS E3_Emp_ReqAudit_1
                      WHERE (Emp_AB_No = EA.EA_AB_No) 
                        AND (Emp_Req_Action = 'E1') AND (Emp_AudStep_Completed = '5') AND (ERA.Emp_Contractor = 'False') 
                         OR (Emp_Req_Action = 'E1') AND (Emp_AudStep_Completed = '2') AND (ERA.Emp_Contractor = 'True'))) 
     AND  ERA.Emp_DtAudStep_Complete     <= dateadd(day,-1,GETDATE())
     AND (ERA.Emp_LocalPrinter = 'True') 
     AND (EA.EA_Printer        = 'False') 
     )
        SET @Printer = 'True'
    ELSE
        SET @Printer = 'False'

    -- Check FuelSystem Access 
    If Exists 
    (
    SELECT ERA.Emp_LastName AS [@EmpLN], ERA.Emp_FirstName AS [@EmpFN], ERA.Emp_DtAudStep_Complete AS [@ReqDate], ERA.Emp_ReqID as [@EmpReqID]
    FROM   [e3].[dbo].E3_Emp_ReqAudit AS ERA INNER JOIN
           [e3].[dbo].E3_Emp_Access   AS EA ON ERA.Emp_AB_No = EA.EA_AB_No INNER JOIN
           [e3].[dbo].E3_Employee     AS E  ON ERA.Emp_AB_No = E.Emp_AB_No
    WHERE (ERA.Emp_Req_Action = 'E1') AND (ERA.Emp_AudStep_Completed = '5') AND (ERA.Emp_Contractor = 'False') 
       OR (ERA.Emp_Req_Action = 'E1') AND (ERA.Emp_AudStep_Completed = '2') AND (ERA.Emp_Contractor = 'True') 
      AND (ERA.Emp_DtAudStep_Complete =
                    (SELECT  MAX(Emp_DtAudStep_Complete) AS Expr1
                      FROM   [e3].[dbo].E3_Emp_ReqAudit AS E3_Emp_ReqAudit_1
                      WHERE (Emp_AB_No = EA.EA_AB_No) 
                        AND (Emp_Req_Action = 'E1') AND (Emp_AudStep_Completed = '5') AND (ERA.Emp_Contractor = 'False') 
                         OR (Emp_Req_Action = 'E1') AND (Emp_AudStep_Completed = '2') AND (ERA.Emp_Contractor = 'True'))) 
     AND  ERA.Emp_DtAudStep_Complete     <= dateadd(day,-1,GETDATE())
     AND (ERA.Emp_FuelSystem = 'True') 
     AND (EA.EA_FuelSystem   = 'False') 
     )
        SET @Fuel = 'True'
    ELSE
        SET @Fuel = 'False'

    -- Check BSTC Access 
    If Exists 
    (
    SELECT ERA.Emp_LastName AS [@EmpLN], ERA.Emp_FirstName AS [@EmpFN], ERA.Emp_DtAudStep_Complete AS [@ReqDate], ERA.Emp_ReqID as [@EmpReqID]
    FROM   [e3].[dbo].E3_Emp_ReqAudit AS ERA INNER JOIN
           [e3].[dbo].E3_Emp_Access   AS EA ON ERA.Emp_AB_No = EA.EA_AB_No INNER JOIN
           [e3].[dbo].E3_Employee     AS E  ON ERA.Emp_AB_No = E.Emp_AB_No
    WHERE (ERA.Emp_Req_Action = 'E1') AND (ERA.Emp_AudStep_Completed = '5') AND (ERA.Emp_Contractor = 'False') 
       OR (ERA.Emp_Req_Action = 'E1') AND (ERA.Emp_AudStep_Completed = '2') AND (ERA.Emp_Contractor = 'True') 
      AND (ERA.Emp_DtAudStep_Complete =
                    (SELECT  MAX(Emp_DtAudStep_Complete) AS Expr1
                      FROM   [e3].[dbo].E3_Emp_ReqAudit AS E3_Emp_ReqAudit_1
                      WHERE (Emp_AB_No = EA.EA_AB_No) 
                        AND (Emp_Req_Action = 'E1') AND (Emp_AudStep_Completed = '5') AND (ERA.Emp_Contractor = 'False') 
                         OR (Emp_Req_Action = 'E1') AND (Emp_AudStep_Completed = '2') AND (ERA.Emp_Contractor = 'True'))) 
     AND  ERA.Emp_DtAudStep_Complete     <= dateadd(day,-1,GETDATE())
     AND (ERA.Emp_BSTC = 'True') 
     AND (EA.EA_BSTC   = 'False') 
     )
        SET @BSTC = 'True'
    ELSE
        SET @BSTC = 'False'

    -- Check Misc Accounts Access 
    If Exists 
    (
    SELECT ERA.Emp_LastName AS [@EmpLN], ERA.Emp_FirstName AS [@EmpFN], ERA.Emp_DtAudStep_Complete AS [@ReqDate], ERA.Emp_ReqID as [@EmpReqID]
    FROM   [e3].[dbo].E3_Emp_ReqAudit AS ERA INNER JOIN
           [e3].[dbo].E3_Emp_Access   AS EA ON ERA.Emp_AB_No = EA.EA_AB_No INNER JOIN
           [e3].[dbo].E3_Employee     AS E  ON ERA.Emp_AB_No = E.Emp_AB_No
    WHERE (ERA.Emp_Req_Action = 'E1') AND (ERA.Emp_AudStep_Completed = '5') AND (ERA.Emp_Contractor = 'False') 
       OR (ERA.Emp_Req_Action = 'E1') AND (ERA.Emp_AudStep_Completed = '2') AND (ERA.Emp_Contractor = 'True') 
      AND (ERA.Emp_DtAudStep_Complete =
                    (SELECT  MAX(Emp_DtAudStep_Complete) AS Expr1
                      FROM   [e3].[dbo].E3_Emp_ReqAudit AS E3_Emp_ReqAudit_1
                      WHERE (Emp_AB_No = EA.EA_AB_No) 
                        AND (Emp_Req_Action = 'E1') AND (Emp_AudStep_Completed = '5') AND (ERA.Emp_Contractor = 'False') 
                         OR (Emp_Req_Action = 'E1') AND (Emp_AudStep_Completed = '2') AND (ERA.Emp_Contractor = 'True'))) 
     AND   ERA.Emp_DtAudStep_Complete    <= dateadd(day,-1,GETDATE())
     AND  (ERA.Emp_Accounts    <> '')    
     AND (EA.EA_Accounts        = '0'    OR  EA.EA_Accounts     = '')
    )
        SET @Accounts = 'True'
    ELSE
        SET @Accounts = 'False'

    -- Check Alarm Access 
    If Exists 
    (
    SELECT ERA.Emp_LastName AS [@EmpLN], ERA.Emp_FirstName AS [@EmpFN], ERA.Emp_DtAudStep_Complete AS [@ReqDate], ERA.Emp_ReqID as [@EmpReqID]
    FROM   [e3].[dbo].E3_Emp_ReqAudit AS ERA INNER JOIN
           [e3].[dbo].E3_Emp_Access   AS EA ON ERA.Emp_AB_No = EA.EA_AB_No INNER JOIN
           [e3].[dbo].E3_Employee     AS E  ON ERA.Emp_AB_No = E.Emp_AB_No
    WHERE (ERA.Emp_Req_Action = 'E1') AND (ERA.Emp_AudStep_Completed = '5') AND (ERA.Emp_Contractor = 'False') 
       OR (ERA.Emp_Req_Action = 'E1') AND (ERA.Emp_AudStep_Completed = '2') AND (ERA.Emp_Contractor = 'True') 
      AND (ERA.Emp_DtAudStep_Complete =
                    (SELECT  MAX(Emp_DtAudStep_Complete) AS Expr1
                      FROM   [e3].[dbo].E3_Emp_ReqAudit AS E3_Emp_ReqAudit_1
                      WHERE (Emp_AB_No = EA.EA_AB_No) 
                        AND (Emp_Req_Action = 'E1') AND (Emp_AudStep_Completed = '5') AND (ERA.Emp_Contractor = 'False') 
                         OR (Emp_Req_Action = 'E1') AND (Emp_AudStep_Completed = '2') AND (ERA.Emp_Contractor = 'True'))) 
     AND  ERA.Emp_DtAudStep_Complete     <= dateadd(day,-1,GETDATE())
     AND (ERA.Emp_Alarm = 'True') 
     AND (EA.EA_Alarm = 'False') 
     )
        SET @Alarm = 'True'
    ELSE
        SET @Alarm = 'False'

    -- Check Telephone Access 
    If Exists 
    (
    SELECT ERA.Emp_LastName AS [@EmpLN], ERA.Emp_FirstName AS [@EmpFN], ERA.Emp_DtAudStep_Complete AS [@ReqDate], ERA.Emp_ReqID as [@EmpReqID]
    FROM   [e3].[dbo].E3_Emp_ReqAudit AS ERA INNER JOIN
           [e3].[dbo].E3_Emp_Access   AS EA ON ERA.Emp_AB_No = EA.EA_AB_No INNER JOIN
           [e3].[dbo].E3_Employee     AS E  ON ERA.Emp_AB_No = E.Emp_AB_No
    WHERE (ERA.Emp_Req_Action = 'E1') AND (ERA.Emp_AudStep_Completed = '5') AND (ERA.Emp_Contractor = 'False') 
       OR (ERA.Emp_Req_Action = 'E1') AND (ERA.Emp_AudStep_Completed = '2') AND (ERA.Emp_Contractor = 'True') 
      AND (ERA.Emp_DtAudStep_Complete =
                    (SELECT  MAX(Emp_DtAudStep_Complete) AS Expr1
                      FROM   [e3].[dbo].E3_Emp_ReqAudit AS E3_Emp_ReqAudit_1
                      WHERE (Emp_AB_No = EA.EA_AB_No) 
                        AND (Emp_Req_Action = 'E1') AND (Emp_AudStep_Completed = '5') AND (ERA.Emp_Contractor = 'False') 
                         OR (Emp_Req_Action = 'E1') AND (Emp_AudStep_Completed = '2') AND (ERA.Emp_Contractor = 'True'))) 
     AND  ERA.Emp_DtAudStep_Complete     <= dateadd(day,-1,GETDATE())
     AND (ERA.Emp_Telephone = 'True') 
     AND (EA.EA_Telephone   = 'False') 
     )
        SET @Telephone = 'True'
    ELSE
        SET @Telephone = 'False'
    END
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
v_luke
  • 1
  • 3
  • 2
    Code. Code is good. Show us the call to the sproc and, if possible, the code of the sproc. Possible issues: does the procedure call another procedure? Is there more than one procedure of the same name in the DB under different schemas, and you are calling the wrong one? – Laughing Vergil May 02 '17 at 17:24
  • This Stored procedure does not call any others, and the name is unique within the database. – v_luke May 02 '17 at 17:54
  • How about calling the procedures with all `output` parameters specified. – TT. May 02 '17 at 18:28
  • Not sure I understand your call...Exec e3.dbo.storedproc_name @EmpNo, ...etc ??? – v_luke May 02 '17 at 18:52
  • ok...I figured the agent's stored procedure call. As per TT, the parameters needed to be declared and called in the exec of the SP. Standard T-SQL. http://stackoverflow.com/questions/1589466/execute-stored-procedure-with-an-output-parameter – v_luke May 08 '17 at 19:56

0 Answers0