EXECUTE PROCEDURE with OUTPUT PARAMETER

HEART-HACKER

HEART-HACKER

@heart-hacker-nZz25T • Oct 21, 2024 
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][B]create[/B][/COLOR][/SIZE][/COLOR][/SIZE][B][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]procedure[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] prcGET @EmpID [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]int[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]@DepName [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]char[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]50[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]output[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @ShiftID [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]int[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]output[/COLOR][/SIZE][/COLOR][/SIZE][/B]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][B]AS[/B][/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][B]BEGIN[/B][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][B]IF[/B][/COLOR][/SIZE][/COLOR][/SIZE][B][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]Exists[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Select[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]*[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] HumanResources[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Employee [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]where[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] EmployeeID[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]@EmpID[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][/B][SIZE=2][COLOR=#808080]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][B]begin[/B][/COLOR][/SIZE]
[/COLOR][/SIZE][B][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Select[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @DepName[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]d[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Name[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @ShiftID[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]h[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]ShiftID[/SIZE][/B]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][B]FROM[/B][/COLOR][/SIZE][/COLOR][/SIZE][B][SIZE=2] HumanResources[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Department d [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]JOIN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] HumanResources[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]EmployeeDepartmentHistory h[/SIZE][/B]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][B]ON[/B][/COLOR][/SIZE][/COLOR][/SIZE][B][SIZE=2] d[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]DepartmentID[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]h[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]DepartmentID[/SIZE][/B]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][B]where[/B][/COLOR][/SIZE][/COLOR][/SIZE][B][SIZE=2] EmployeeID [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @EmpID [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]AND[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] h[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Enddate [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]IS[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]null[/COLOR][/SIZE][/COLOR][/SIZE][/B][SIZE=2][COLOR=#808080]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][B]print[/B][/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][B] @ShiftID[/B][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][B]print[/B][/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][B] @DepName [/B][/SIZE]
[B][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]RETURN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 0[/SIZE][/B]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][B]END[/B][/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][B]ELSE[/B][/COLOR][/SIZE]
[/COLOR][/SIZE][B][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]RETURN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 1[/SIZE][/B]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][B]END[/B][/COLOR][/SIZE]
This is my code for creating procedure, But whenever I am executing procedure by following statement

EXEC prcGET @EmpID= 2,@DepName output,@ShiftID output

It gives following error

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@DepName".

So tell me proper Execute code... Thanks in advance 😁
[/COLOR][/SIZE]
Like 0 Replies 3

Replies

Welcome, guest

Join CrazyEngineers to reply, ask questions, and participate in conversations.

Sign up Log in

CrazyEngineers powered by Jatra Community Platform

  • shalini_goel14

    shalini_goel14

    @shalini-goel14-ASmC2J • Feb 1, 2009

    Hi HEART_HACKER,

    Is your problem solved or still same issue you are facing?

    By the way I have never seen a procedure with annotations(one used in yours) before.Is the code given by you really in PL/SQL? 😕

    Please do reply back. 😀
    0
  • HEART-HACKER

    HEART-HACKER

    @heart-hacker-nZz25T • Feb 10, 2009

    Its a SQL problem

    Problem is still there...😒
    0
  • micheal.vel

    micheal.vel

    @michealvel-TtvrLe • Feb 25, 2011

    hi friends..............
    Hi HEART_HACKER,

    you want to declare the out param and then execute.

    declare
    @DepName     char(50)output    ;

    begin
    EXEC prcGET @EmpID= 2,@DepName,@ShiftID ;
    end;
    0