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

Posted by Kumaran under Sql Server on 9/4/2014 | Points: 10 | Views : 544 | Status : [Member] | Replies : 4
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




Responses

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

Up
0
Down

Resolved
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

Up
0
Down

Resolved
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

Up
0
Down
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

Up
0
Down
ok.Thank for your quick response.

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

Login to post response