Stored procedure query is not working

Question

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 16-Jan-13 21:14pm

Mohd. Mukhtar

Add a Solution

2 solutions

Solution 3

SQL

use databasename
create procedure procedurename
(
@CustomerId int 
)
as
begin

SELECT * FROM Customer WHERE CustomerId=@CustomerId

end

try this procedure

Posted 16-Jan-13 21:49pm

Sasikumar.mohan

Updated 16-Jan-13 23:05pm

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.

Add a Solution

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

__TR__ · Accepted Answer · 2013-01-16T21:40:00

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.

Stored procedure query is not working

2 solutions

Solution 2

Solution 3

Add your solution here

Preview 0

Stored procedure query is not working

2 solutions

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us