Schleid Alex

Schleid Alex

  • 1.6k
  • 361
  • 18.6k

Calling stored procedure from another stored procedure in sqlserver

Oct 22 2020 7:23 PM
Hi,
 
I have the following stored procedure in a Sql Server Database
  1. CREATE PROCEDURE [dbo].[UP_SELECT_PO_NUMBER]  
  2.                                     @StoreBranchID int,  
  3.                                     @mPurchaseOrderNumber NVARCHAR(13) OUTPUT  
  4.                                     AS  
  5. --DECLARE @StoreBranchID int  
  6. --SELECT @StoreBranchID = 4  
  7. --DECLARE @mPurchaseOrderNumber nvarchar(13)  
  8.   
  9. DECLARE @lpo int  
  10. DECLARE @mPO int  
  11. SET @mPurchaseOrderNumber=''  
  12. SELECT  
  13.         @mPO= cast(right(RTRIM(PurchaseOrderNumber),5) as int) + 1   
  14. FROM  
  15.         PurchaseOrder  
  16. WHERE  
  17.         PurchaseOrderID = (SELECT  
  18.                                     Max(purchaseOrderID)  
  19.                            FROM  
  20.                                     purchaseorder  
  21.                            WHERE  
  22.                                     storebranchID = @StoreBranchID)  
  23. SET @lpo = LEN(@mPO)  
  24.  WHILE(@lpo<>5 )  
  25.      BEGIN  
  26.         SET @mPurchaseOrderNumber += '0'  
  27.         SET @lpo = @lpo + 1  
  28.      END  
  29. SET @mPurchaseOrderNumber ='PO'+'-'CONVERT(NVARCHAR, YEAR(GETDATE())) + '-'+ @mPurchaseOrderNumber + CONVERT(nvarchar,@mPO)  
  30. RETURN @mPurchaseOrderNumber  
it is just return a variable
 
Now I am calling it withing another store procedure with the following command
  1. DECLARE @PON NVARCHAR(13)  
  2.   
  3. EXEC UP_SELECT_PO_NUMBER @StoreBranchID = 4, @mPurchaseOrderNumber = @PON OUTPUT;  
  4.   
  5. 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

Answers (3)