No record found when Pass multivalued varriables to stored procedure [Resolved]

Posted by Kumaran under Sql Server on 9/4/2014 | Points: 10 | Views : 847 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

i have table called cus_details which contains all the details about the customer details.
i need to get one or two customer details when user selected.
if i pass one values means it shows records.
ReturnNewViewcmd.Parameters.AddWithValue("@id", '28119');
if it's two values means query returns no records

con.Open();

ReturnNewViewcmd.Connection = con;
//old proc CES_CIR_JIV_RESTOCKNEWRETURNS_sample
ReturnNewViewcmd.CommandText = "reports_customer";
ReturnNewViewcmd.CommandType = CommandType.StoredProcedure;

ReturnNewViewcmd.Parameters.AddWithValue("@id", '28119,28778');
//no record found
ReturnNewViewda = new SqlDataAdapter(ReturnNewViewcmd);
ReturnNewViewda.Fill(ReturnNewViewdt);

create proc reports_customer(
@id varchar(100)
)
as begin
select * from cus_details where cus_id in (@id)
end

Thanks

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 9/4/2014 [Member] [MVP] Platinum | Points: 50

0	the below code will work for either multi-values or single value GO alter proc reports_customer( @id varchar(100) ) as begin select * from dbo.cus_details where ','+@id+',' like '%,'+ cast(cus_id as varchar(100))+ ',%' end Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Kumaran, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Kumaran on: 9/4/2014 [Member] Starter | Points: 25

0	Above example is now working fine. you saved lot of time . Thank you very much And also i tried same like your querey this is also give a same output. alter proc reports_customer( @id varchar(100) ) as begin select * from dbo.cus_details where CHARINDEX(',' + CONVERT(varchar(100),cus_id) + ',', ',' + @id + ',') > 0 end Kumaran, if this helps please login to Mark As Answer. \| Alert Moderator

Above example is now working fine. you saved lot of time .
Thank you very much

And also i tried same like your querey this is also give a same output.

alter proc reports_customer(
@id varchar(100)
)
as begin
select * from dbo.cus_details where CHARINDEX(',' + CONVERT(varchar(100),cus_id) + ',', ',' + @id + ',') > 0
end

Kumaran, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/4/2014 [Member] [MVP] Platinum | Points: 25

0	But remember that both approaches will decrease performance if you have huge numbers of values for input Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Kumaran, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Kumaran on: 9/5/2014 [Member] Starter | Points: 25

0	ok.Thank for your quick response. Kumaran, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts