Retrieve from database

Question

1.00/5 (1 vote)

See more:

SQL-Server-2005

, +

I have one String str which contains multiple customer Id like C00001,C00002,C00003.. seperated by comma.
Now i wants to pass this string str as single parameter to my stored procedure. I tried below code but it doen't works.
can anybody please help me???????

eg

C#

foreach (string s in idlist)
        {
            str = str +"'"+ s +"'" + ",";
        }
        str = str.Substring(0, str.Length - 1);

Now i wants to pass this str to my stored procedure as parameter

stored procedure is like this
select * from Customer_Master where CustomerId IN(@CustomerId)

Posted 19-Feb-12 19:01pm

Member 8570559

Updated 19-Feb-12 19:18pm

v2

Add a Solution

Comments

Varun Sareen 20-Feb-12 1:03am

what is idlist?

5 solutions

Solution 4

Hi, use below code:

Send Customer ID String in single parameter to Stored Procedure. Then use below method when you writing SP:

SQL

Create procedure [dbo].[Get_Customer]
(
@strCustomerID varchar(5000)
)
As
Begin
Declare @x int, @i int
Declare @Cust_ID varchar(50)
Select *, ROW_NUMBER() OVER(ORDER BY (Select 0)) AS RowNumber into #Cust from dbo.split(@strCustomerID,',')

Select @x = COUNT(*) from #Cust
	while(@i<=@x)
	Begin
		Select @Cust_ID = items from #Cust where RowNumber = @i
SET @query='Select * from CustomerTable where CustomerID ='''+ @Cust_ID + ''''
print @query
		EXEC sp_executesql @query 
		------------------------------------
		
		Set @i = @i+1
	End


End

Posted 19-Feb-12 19:36pm

Sarvesh Kumar Gupta

Updated 19-Feb-12 19:40pm

v3

Solution 6

You can create user defined function to split Customer IDs like this

SQL

CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO
--Test
select * from fnSplit('1,22,333,444,,5555,666', ',')
select * from fnSplit('1##22#333##444','##')  --note second item has embedded #
select * from fnSplit('1 22 333 444  5555 666', ' ')

This function you can use in your Stored Procedure. sample code snippet for using the same function is as follows

SQL

select * from Customer_Master where CustomerId IN(SELECT * FROM	dbo.fnSplit(@CustomerId, ','))

Where @CustomerId is parameter of type varchar. While passing the values of @CustomerId string can be like C00001,C00002,C00003...

I hope this will help you out. If you need further help please let me know.
If this works fine please accept the solution.
Thanks

Posted 19-Feb-12 20:34pm

jai000

Solution 2

You can't do that according to your stored procedure!

SQL

select * from Customer_Master where CustomerId IN(@CustomerId)

This is similar what you want:
- Your str should be:

C#

str="'C00001','C00002','C00003'";

- Your stored procedure should be:

SQL

exec ('select * from Customer_Master where CustomerId IN(' + @CustomerId + ')')

Posted 19-Feb-12 19:26pm

Bun Leap_kh

Updated 19-Feb-12 19:35pm

v2

Comments

Member 8570559 20-Feb-12 1:33am

can you give the code to create the stored procedure....

Solution 3

Hi,

please see this article for your solution

thanks
-Amit

Posted 19-Feb-12 19:34pm

AmitGajjar

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

sachin10d · Accepted Answer · 2012-02-19T19:50:00

Solution 5

SQL

--Sp_Test 'C00001','C00002','C00003'
create proc Sp_Test
@strParameter nvarchar(1000)
as
exec ('select * from Customer_Master  where CustomerId in ('+@strParameter+')')
go

Posted 19-Feb-12 19:50pm

sachin10d

Comments

Member 8570559 20-Feb-12 2:01am

it giving that "Procedure or function Sp_Test has too many arguments specified.
"

Member 8570559 20-Feb-12 2:10am

thanks man its working!!!

sachin10d 20-Feb-12 3:08am

welcome