how to set out parameter

Question

User1828943426 posted

i have the following stored procedure, and declared @ReceiptNo as int which i need to set as out parameter

how can i do that

ALTER PROCEDURE [dbo].[sp_InsertIncomingMail]
(

@Date_MailReceived datetime,
@Date_MailDelivered datetime,
@TypeOfMaterial nvarchar(max),
@EmpName nvarchar(max),
@CourierName nvarchar(max),
@DeliveryBoy_Name nvarchar(max),
@DeliveryBoy_MobileNO nvarchar(MAX),
@Emp_email nvarchar(max),
@emp_mobile nvarchar(max),
@Status bit
)
AS

DECLARE @ReceiptNo int
Select @ReceiptNo=Max(isnull(ReceiptNo,0)) from dbo.IncomingMail

BEGIN
     insert into dbo.IncomingMail	
     values
	(
    @ReceiptNo,
    @Date_MailReceived ,
    @Date_MailDelivered ,
    @TypeOfMaterial,
    @EmpName ,
    @CourierName,
	@DeliveryBoy_Name,
	@DeliveryBoy_MobileNO ,
	@Emp_email,
	@emp_mobile ,
	@Status
	)
END

Answer 1

User-260044566 posted

Hi,

While passing parameter to the stored procedure itsellf you can declare the output parameter as,

@ReceiptNo int OUTPUT

Then you can assign values to that as in below example,

SELECT @ReceiptNo = somevalue

Hope it helps

Answer 2

User-771367964 posted

Example-

CREATE OR REPLACE PROCEDURE INSERTBLKHEADER(
v_fkey IN callblockheader.pk_sourcefile%type,
v_bSNum IN callblockheader.blockserialnumber%type,
v_bCount IN callblockheader.blockcount%type,
v_fOffset IN callblockheader.fileoffset%type,
v_id OUT NUMBER)
AS

BEGIN
INSERT INTO CallBlockHeader (pk_blockheader,
pk_sourceFile, blockSerialNumber, blockCount,
fileOffset)
VALUES(callblockheader_seq.nextval, v_fkey,
v_bSNum, v_bCount, v_fOffset);
COMMIT;

SELECT pk_blockheader into v_id
FROM callblockheader
WHERE pk_blockheader =
CallBlockHeader_Seq.currval;

END;