How to call the stored procedure having out parameter?

Posted by Prabu_Spark under Sql Server on 12/28/2012 | Points: 10 | Views : 2322 | Status : [Member] | Replies : 2
Hi sir,
I did one store procedure which having one error parameter. It is created successfully, if execute that stored procedure ,it show error
"Msg 201, Level 16, State 4, Procedure spuserdetail, Line 0
Procedure or Function 'spuserdetail' expects parameter '@Error', which was not supplied." I attached the stored procedure below. Kindly give me the solution for this problem.

Procedure:
******************
create PROCEDURE [dbo].[spuserdetail]
@empid int,
@empname varchar(50),
@empsal varchar(50),
@Error VARCHAR(100) out
AS
BEGIN
SET NOCOUNT ON;
-- To Check Employee Name is exits or not
IF NOT EXISTS(SELECT * FROM emp WHERE ename=@empname)
BEGIN
insert into emp(eid,ename,esalary) values (@empid,@empname,@empsal)
SET @Error=@empname+' Registered Successfully'
END
ELSE
BEGIN
SET @ERROR=@empname + ' Already Exists'
END
END

Calling the procedure:
************************
exec [spuserdetail] 3,'Aravind',12000


Error while executing the procedure:
*************************************

Msg 201, Level 16, State 4, Procedure spuserdetail, Line 0
Procedure or Function 'spuserdetail' expects parameter '@Error', which was not supplied.

With regards,
J.Prabu.
[Email:prbspark@gmail.com]



Responses

Posted by: Sourabh07 on: 12/28/2012 [Member] Starter | Points: 25

Up
0
Down
hi..

excecute following statements by selecting all...in the sql query...


Declare @error varchar(100)

exec [spuserdetail] 3,'Aravind',12000,@error output
Select @error


Sourabh07

Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pavanandey on: 12/28/2012 [Member] Bronze | Points: 25

Up
0
Down
Declare as follows
@Error VARCHAR(100) = null OUTPUT

now you need not pass @Error

Mark answer if this helps you

Thanks
Pavan Kumar
Mark Answer if this fits the need

Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response