Stored procedure which takes a multi value parameter

Question

I create these but it takes multi parameter but when it executes stored procedure, THEN I get an error:

Conversion failed when converting the NVARCHAR value '141,139' to data type int.

Here is my code:

BEGIN

SET NOCOUNT ON;

DECLARE @temp NVARCHAR(MAX)
SET @temp = REPLACE(@Voucher_ID, '''', '')

SELECT 
    av.Voucher_No, avl.Ref_Dr_Cr, Av.Voucher_Amount,
    avl.Ledger_Amount, AVLI.Quantity, AVli.Rate, AVli.PCS,
    AVLI.Inventory_Total, AVli.Discount,al.Ledger_Name,  
    st.Stock_Name, st.HSN_Code, al.address,   
    (SELECT CASE WHEN al.Ledger_Name <> 'Rounded Off' AND avl.Ref_Dr_Cr = 'Dr' THEN al.GSTIN_No ELSE '' END) AS GSTIN_No,   
    (SELECT CASE WHEN avl.Ref_Dr_Cr = 'Dr' AND al.Ledger_Name <> 'Rounded Off' THEN al.Ledger_Name ELSE '' END) AS Party_Name,
    (SELECT CASE WHEN al.Ledger_Name <> 'Rounded Off' AND avl.Ref_Dr_Cr = 'Dr' THEN al.Address ELSE ''END) AS  Ledger_Address,
    (SELECT CASE WHEN al.Ledger_Name = 'Rounded Off' AND avl.Ref_Dr_Cr = 'Dr' THEN CAST(avl.Ledger_Amount AS NVARCHAR(100)) ELSE '' END) AS Amount,
    (SELECT CASE WHEN al.Ledger_Name LIKE '%IGST%'THEN CAST(avl.Ledger_Amount AS NVARCHAR(100)) ELSE '' END) AS IGST,
    (SELECT CASE WHEN al.Ledger_Name LIKE '%SGST%'THEN CAST(avl.Ledger_Amount AS NVARCHAR(100)) ELSE '' END) AS SGST,
    (SELECT CASE WHEN al.Ledger_Name LIKE '%CGST%'THEN CAST(avl.Ledger_Amount AS NVARCHAR(100)) ELSE '' END) AS CGST,
    AVLI.UOM
FROM
    Acc_Voucher AV 
INNER JOIN  
    Acc_Voucher_Ledger AVL ON Av.Voucher_Id = avl.Voucher_ID
LEFT JOIN 
    Acc_Voucher_Ledger_Inventory AVLI ON AVL.Voucher_Ledger_Id = avli.Voucher_Ledger_Id 
INNER JOIN  
    Acc_Ledger AL ON AVL.Ledger_Id = al.Ledger_ID
LEFT JOIN
    Acc_Stock_Item St ON avli.Stock_Item_Id = st.Stock_Item_Id      
WHERE
    av.Voucher_ID IN (@Voucher_ID) 

END

Any ideas? Thank you.

i create these but it takes multi parameter but when execute sp then gives error:Conversion failed when converting the nvarchar value '141,139' to data type int. — Rahul Rajput, Mar 23 '19 at 09:57
Welcome to Stack Overflow. However, pasting a lump of code with no explanation is not encouraged. [How to Ask](https://stackoverflow.com/help/how-to-ask). Regards. — Elletlar, Mar 23 '19 at 10:09
I have tried to fix your post, but please in future provide a full and helpful explanation of the problem inside the question. — Elletlar, Mar 23 '19 at 10:16
Look at this question: https://stackoverflow.com/q/5401641/13087 — Joe, Mar 23 '19 at 10:25
Procedural code is **highly vendor-specific** - so please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Mar 23 '19 at 10:25
I suggest a [table-valued parameter](http://www.sommarskog.se/arrays-in-sql-2008.html) instead of delimited list to avoid parsing. See [this article]( http://www.sommarskog.se/arrays-in-sql.html) for how to parse arrays and lists. — Dan Guzman, Mar 23 '19 at 12:38

Srikar mogaliraju · Answer 1 · 2019-03-23T11:03:13.497

0

You should use Dynamic SQL to pass multiple values through a Variable when using IN condition. Try something like below:

  declare @voucherid nvarchar(1000)='1,2'

  declare @sql nvarchar(max)='
  select * from Customer
  where id in ('+@voucherid+')'
  print @sql

  EXECUTE sp_executesql @sql

edited Mar 23 '19 at 11:03

answered Mar 23 '19 at 10:16

Srikar mogaliraju

215
1
10

Stored procedure which takes a multi value parameter

1 Answers1