Click here to Skip to main content
15,942,159 members
Please Sign up or sign in to vote.
3.33/5 (3 votes)
See more:
Hi,

I have a stored procedure and I am passing parameter as below.
C#
SqlDataCommand cmd = new SqlDataCommand("dbo.GetCustomer");
cmd.AddParam("@CustomerId", strCustomerId, DbType.String);

Stored procedure query to get customer is as below.
SQL
SELECT * FROM Customer WHERE CustomerId IN(@CustomerId)

Here if I pass single customer id in stored procedure it is working fine but if I pass multiple customer id with comma separated it is not fetching result.

I executed sp through SQL Server Management Studio even getting same result.
SQL
USE [TestDB]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[qa_RK_TransactionsProcess]
		@CustomerId = N'199350029'		
SELECT	'Return Value' = @return_value

GO

The above give me the expected result.

But when I execute with multiple customer as below.
SQL
USE [TestDB]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[qa_RK_TransactionsProcess]
		@CustomerId = N'199350029,199350026'		
SELECT	'Return Value' = @return_value

GO

It does'nt return any result.
Even I tested the query it is building properly as below. But I don't know why it is behaving like this.
SQL
SELECT * FROM Customer WHERE CustomerId IN(199350029,199350026)


Any help will be very much appreciated.
Posted

You need to convert the csv value into rows of a table for it to work inside the IN clause.
Here is a sample
SQL
DECLARE @Customer TABLE
(
    CustomerId VARCHAR(100),
    CustomerName VARCHAR(100)
)

INSERT INTO @Customer
SELECT 1, 'Bob' UNION ALL
SELECT 2, 'Steve' UNION ALL
SELECT 3, 'Smith' UNION ALL
SELECT 4, 'Matt' UNION ALL
SELECT 5, 'John'


SELECT * FROM @Customer


DECLARE @CustomerId VARCHAR(100)
SET @CustomerID = N'1,3,4'

SELECT * FROM @Customer WHERE CustomerId IN (@CustomerID) --This won't work

SELECT * FROM @Customer WHERE CustomerId IN (SELECT VarcharValue FROM  dbo.ufn_CsvToVarchar(@CustomerID)) --This will work


This is the function used to convert csv values into rows.
SQL
CREATE Function dbo.ufn_CsvToVarchar ( @Array varchar(MAX))
returns @VarcharTable table
 (VarcharValue varchar(50))
AS
begin

 declare @separator char(1)
 set @separator = ','

 declare @separator_position int
 declare @array_value varchar(1000)

 set @array = @array + ','

 while patindex('%,%' , @array) <> 0
 begin

   select @separator_position =  patindex('%,%' , @array)
   select @array_value = left(@array, @separator_position - 1)

  Insert @VarcharTable
  Values (Cast(@array_value as varchar(50)))

   select @array = stuff(@array, 1, @separator_position, '')
 end

 return
end


Hope this helps.
 
Share this answer
 
Comments
Mohd. Mukhtar 17-Jan-13 3:49am    
Thanks TR, It is working fine. +5
__TR__ 17-Jan-13 4:52am    
Glad it worked out :)
SQL
use databasename
create procedure procedurename
(
@CustomerId int 
)
as
begin

SELECT * FROM Customer WHERE CustomerId=@CustomerId

end


try this procedure
 
Share this answer
 
v2
Comments
Mohd. Mukhtar 17-Jan-13 3:52am    
This will work for single customer, But I need to fetch multiple customer data WITH IN clause.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900