CrazyEngineers Logo

EXECUTE PROCEDURE with OUTPUT PARAMETER

[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]

Replies

  • shalini_goel14
    shalini_goel14
    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. 😀
  • HEART-HACKER
    HEART-HACKER
    Its a SQL problem

    Problem is still there...😒
  • micheal.vel
    micheal.vel
    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;

You are reading an archived discussion.

Related Posts

looking for a circuit's name

hi everybody, i am new to this forum, a mechanical engineer who works as a tour guide but in my free time trying to develop some stuff. i am looking...

Problem: "Task manager disabled by administrator" in Windows XP SP2

hi!!!!! Every one i have the problem in open the task manager,i m using winXpSp2,when i press alt+ctrl+delete,then i see the msg "task manager disable by your administrator,i m the...

Guidance for project on shell and tube exchanger

can any one guide the areas in which our project in shell and tube exchanger can be done. (areas in which our research to be carried) and pls provide complete...

Major project idea??

hi am new to CE. i am currently a student at the university of technology Jamaica. I was recently presented with the task of coming up with an electrical major...

EEG analysis using neural networks

Hi i'm doing my final year project on Effect of music on EEG.I'v to check for any deviation in EEG before and while listening to music. Can I use neural...