Calling stored procedure from another stored procedure in sqlserver

Hi,

I have the following stored procedure in a Sql Server Database

CREATE PROCEDURE [dbo].[UP_SELECT_PO_NUMBER]
@StoreBranchID int,
@mPurchaseOrderNumber NVARCHAR(13) OUTPUT
AS
--DECLARE @StoreBranchID int
--SELECT @StoreBranchID = 4
--DECLARE @mPurchaseOrderNumber nvarchar(13)
DECLARE @lpo int
DECLARE @mPO int
SET @mPurchaseOrderNumber=''
SELECT
@mPO= cast(right(RTRIM(PurchaseOrderNumber),5) as int) + 1
FROM
PurchaseOrder
WHERE
PurchaseOrderID = (SELECT
Max(purchaseOrderID)
FROM
purchaseorder
WHERE
storebranchID = @StoreBranchID)
SET @lpo = LEN(@mPO)
WHILE(@lpo<>5 )
BEGIN
SET @mPurchaseOrderNumber += '0'
SET @lpo = @lpo + 1
END
SET @mPurchaseOrderNumber ='PO'+'-'+ CONVERT(NVARCHAR, YEAR(GETDATE())) + '-'+ @mPurchaseOrderNumber + CONVERT(nvarchar,@mPO)
RETURN @mPurchaseOrderNumber

it is just return a variable

Now I am calling it withing another store procedure with the following command

DECLARE @PON NVARCHAR(13)
EXEC UP_SELECT_PO_NUMBER @StoreBranchID = 4, @mPurchaseOrderNumber = @PON OUTPUT;
print @PON

and I am getting

"Conversion failed when converting the nvarchar value 'PO-2020-00023' to data type int." error. I have no type int in the calling procedure

Can someone help me spot my mistake?

Thanks in advance

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty6y ago
Accepted answer
Hey, Alex instead of RETURN @mPurchaseOrderNumber please use SELECT @mPurchaseOrderNumber at the end in your stored procedure.
+1
Schleid Alex6y ago
Thank you all for your time!
+1
Piyush Pansuriya6y ago
Hi Schleid

I think you are missing in the last statement to conversion or NVARCHAR lenght in each and every conversion.
SET @mPurchaseOrderNumber ='PO'+'-'+ CONVERT(NVARCHAR(4), YEAR(GETDATE())) + '-'+ CONVERT(NVARCHAR(8),@mPurchaseOrderNumber) + CONVERT(NVARCHAR(8),@mPO)
Please try above code.
+1