View Feed
group-icon
Coffee Room
Discuss anything here - everything that you wish to discuss with fellow engineers.
12831 Members
Join this group to post and comment.
HEART-HACKER
HEART-HACKER • Jan 28, 2009

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]
shalini_goel14
shalini_goel14 • 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. 😀
HEART-HACKER
HEART-HACKER • Feb 11, 2009
Its a SQL problem

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

Share this content on your social channels -