Wednesday, March 21, 2012

Creating a store procedure with output parameter

Hi all,
Could someone give me an example on how to create and execute the store procedure with using output parameter?
In normal, I create the store procedure without using output parameter, and I did it as follow:
CREATE PROC NewEmployee
(ID int(9), Name Varchar (30), hiredate DateTime, etc...)
AS
BEGIN
//my code
END
GO
When i executed it, I would said: Execute NewEmployee 123456789, 'peter mailler', getDate(), etc...
For output parameter:
CREATE PROC NewEmployee
(ID int(9), Name Varchar (30), hiredate DateTime,@.message Varchar(40) out)
AS
BEGIN
insert into Employee .....
//if error encountered
set@.message = "Insertion failure"
END
GO
Exec NewEmployee 123456789, 'peter mailler', getDate(),do I need to input something for the output parameter here?

Anyone could give me an example on how to handle the output parameter within the store procedure coz I am not sure how to handle it?
Many thanks.



Hi,

when calling EXEC newEmployee... you need ti declarare a parameter, assign it to the call and then you'd get the output value from it.

DECLARE @.msg nvarchar(40);
Exec NewEmployee 123456789, 'peter mailler', getDate(),@.msg;

-- Here you could access @.msg to get what was outputted

No comments:

Post a Comment