Styjun

What does formal training in a field mean?

Is ‘despite that’ right?

What do "KAL." and "A.S." stand for in this inscription?

Further factorisation of a difference of cubes?

Why do unstable nuclei form?

Pre-1993 comic in which Wolverine's claws were turned to rubber?

When do you stop "pushing" a book?

Is every story set in the future "science fiction"?

Does the 500 feet falling cap apply per fall, or per turn?

We are two immediate neighbors who forged our own powers to form concatenated relationship. Who are we?

Why is it wrong to *implement* myself a known, published, widely believed to be secure crypto algorithm?

Why does increasing the sampling rate make implementing an anti-aliasing filter easier?

Ex-manager wants to stay in touch, I don't want to

When quoting someone, is it proper to change "gotta" to "got to" without modifying the rest of the quote?

Names of the Six Tastes

How did Thanos not realise this had happened at the end of Endgame?

Was there a contingency plan in place if Little Boy failed to detonate?

Best species to breed to intelligence

spatiotemporal regression

Company threw a surprise party for the CEO, 3 weeks later management says we have to pay for it, do I have to?

Extending Kan fibrations, without using minimal fibrations

My perfect evil overlord plan... or is it?

Peculiarities in low dimensions or low order or etc

Finding the root cause of Spanning-Tree recalculations (on Cisco Nexus 9000s)

Stored procedure which takes a multi value parameter

Passing a SQL parameter to an IN() clause using typed datasets in .NETSelect columns from result set of stored procedureQuery to list all stored proceduresWhat is a stored procedure?Insert results of a stored procedure into a temporary tableFunction vs. Stored Procedure in SQL Serverstored procedure returns nothingMSSQL Stored Procedure: Compare Parameter with Column if ISNUMERICParameter doesn't perform as well as hard coding the valueSearch text in stored procedure in SQL ServerStored Procedure if Exist with dynamically table

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

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.

sql-server tsql

share|improve this question

edited Mar 23 at 11:09

a_horse_with_no_name

312k46475578

asked Mar 23 at 9:56

Rahul RajputRahul Rajput

11

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 at 9:57
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Welcome to Stack Overflow. However, pasting a lump of code with no explanation is not encouraged. How to Ask. Regards.
  
  – Elletlar
  Mar 23 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 at 10:16
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Look at this question: stackoverflow.com/q/5401641/13087
  
  – Joe
  Mar 23 at 10:25
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I suggest a table-valued parameter instead of delimited list to avoid parsing. See [this article]( sommarskog.se/arrays-in-sql.html) for how to parse arrays and lists.
  
  – Dan Guzman
  Mar 23 at 12:38
  

  
  
  
  

 | 
show 2 more comments

0

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.

sql-server tsql

share|improve this question

edited Mar 23 at 11:09

a_horse_with_no_name

312k46475578

asked Mar 23 at 9:56

Rahul RajputRahul Rajput

11

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 at 9:57
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Welcome to Stack Overflow. However, pasting a lump of code with no explanation is not encouraged. How to Ask. Regards.
  
  – Elletlar
  Mar 23 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 at 10:16
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Look at this question: stackoverflow.com/q/5401641/13087
  
  – Joe
  Mar 23 at 10:25
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I suggest a table-valued parameter instead of delimited list to avoid parsing. See [this article]( sommarskog.se/arrays-in-sql.html) for how to parse arrays and lists.
  
  – Dan Guzman
  Mar 23 at 12:38
  

  
  
  
  

 | 
show 2 more comments

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.

sql-server tsql

share|improve this question

edited Mar 23 at 11:09

a_horse_with_no_name

312k46475578

asked Mar 23 at 9:56

Rahul RajputRahul Rajput

11

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

sql-server tsql

sql-server tsql

share|improve this question

edited Mar 23 at 11:09

a_horse_with_no_name

312k46475578

asked Mar 23 at 9:56

Rahul RajputRahul Rajput

11

share|improve this question

edited Mar 23 at 11:09

a_horse_with_no_name

312k46475578

asked Mar 23 at 9:56

Rahul RajputRahul Rajput

11

edited Mar 23 at 11:09

a_horse_with_no_name

312k46475578

edited Mar 23 at 11:09

a_horse_with_no_name

312k46475578

asked Mar 23 at 9:56

Rahul RajputRahul Rajput

11

asked Mar 23 at 9:56

Rahul RajputRahul Rajput

11

1 Answer
1

active

oldest

votes

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

share|improve this answer

edited Mar 23 at 11:03

answered Mar 23 at 10:16

Srikar mogalirajuSrikar mogaliraju

23817

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55312538%2fstored-procedure-which-takes-a-multi-value-parameter%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

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

share|improve this answer

edited Mar 23 at 11:03

answered Mar 23 at 10:16

Srikar mogalirajuSrikar mogaliraju

23817

add a comment | 

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

share|improve this answer

edited Mar 23 at 11:03

answered Mar 23 at 10:16

Srikar mogalirajuSrikar mogaliraju

23817

add a comment | 

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

share|improve this answer

edited Mar 23 at 11:03

answered Mar 23 at 10:16

Srikar mogalirajuSrikar mogaliraju

23817

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

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

 EXECUTE sp_executesql @sql

share|improve this answer

edited Mar 23 at 11:03

answered Mar 23 at 10:16

Srikar mogalirajuSrikar mogaliraju

23817

answered Mar 23 at 10:16

Srikar mogalirajuSrikar mogaliraju

23817

answered Mar 23 at 10:16

Srikar mogalirajuSrikar mogaliraju

23817