how to extract comma seperated values in sp using SQL server 2000

Question

3.00/5 (1 vote)

See more:

SQL-server-2000

Hello friends,
I have Table name as country like.

SQL

Id    CountryName
1     India
2     Aus
3     Pak
4     US
5     Eng

And my stored procedure is like.

SQL

CREATE PROCEDURE getCountry
@Id(VARCHAR 10)
AS
BEGIN
      IF @Id IS NOT NULL
         SELECT CountryName FROM Country where Id = @Id
      END
END
GO

I am sending the parameter as Id = 1,2,3,4,5 in string format,
How can we extract the comma separated?

Posted 17-Jan-11 19:01pm

nilesh.d.mankar

Updated 17-Jan-11 19:15pm

TweakBird

v2

Add a Solution

Comments

TweakBird 18-Jan-11 1:18am

Edited for code blocks & Capitalization

3 solutions

Solution 2

hi just execute the query in sql server..this will give you an idea about comma seperation.

declare  @roleIds varchar(250),
    @userId int
BEGIN
 set @roleIds='1,2,3,4,5,6,7,'
DECLARE @sItem VARCHAR(8000)
While (Charindex(',',@roleIds)>0)
    Begin
        Select  @sItem = ltrim(rtrim(Substring(@roleIds,1,Charindex(',',@roleIds)-1)))
        Set @roleIds = Substring(@roleIds,Charindex(',',@roleIds)+1,len(@roleIds))
        declare @n int
        SET @n = CONVERT(int, @sItem)
        print @n
    End
END

I hope this may help you....

Posted 18-Jan-11 1:05am

Blesson Mathew

Updated 18-Jan-11 1:07am

v2

Solution 1

There is no direct function to split a string in sql query. You need to make one of your own. Below is an example:

http://geekswithblogs.net/AngelEyes/archive/2007/04/12/111504.aspx[^]

Posted 17-Jan-11 19:13pm

Wild-Programmer

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)

Darpanw · Accepted Answer · 2011-01-18T01:17:00

CREATE A FUNCTION AS FOLLOWS

SQL

CREATE FUNCTION fn_Split_Up_Ids
(
   @Param_Ids varchar(500)
)
RETURNS @Id_Table TABLE(IDField int)

AS
BEGIN  
   IF (LEN(@Param_Ids) &lt;= 0) 
      RETURN

   DECLARE @CommaPos smallint
   SET @CommaPos = CHARINDEX(',', RTRIM(LTRIM(@Param_Ids)))	  

   IF @CommaPos = 0
       INSERT INTO @Id_Table 
              VALUES(CONVERT(BIGINT ,RTRIM(LTRIM(@Param_Ids))))
   ELSE 
       BEGIN
           WHILE LEN(@Param_Ids) &gt; 1
	   BEGIN
	     SET @CommaPos = CHARINDEX(',', RTRIM(LTRIM(@Param_Ids)))
             INSERT INTO @Id_Table 
                      VALUES(CONVERT(INT ,SUBSTRING(RTRIM(LTRIM(@Param_Ids)),1, @CommaPos - 1)))
	     SET @Param_Ids = SUBSTRING(RTRIM(LTRIM(@Param_Ids)), @CommaPos + 1 , LEN(RTRIM(LTRIM(@Param_Ids))))
	     SET @CommaPos = CHARINDEX(',', RTRIM(LTRIM(@Param_Ids)))
	     IF @CommaPos = 0
	     BEGIN
                 INSERT INTO @Id_Table VALUES(CONVERT(INT ,RTRIM(LTRIM(@Param_Ids))))
                 BREAK
	     END
	   END
       END
       RETURN 
  END

AND THEN YOU NEED TO MODIFY YOUR SP AS BELOW:

SQL

CREATE PROCEDURE spGetCountry
@Id VARCHAR(100)
AS
BEGIN
         SELECT Country FROM tblCountry where Id IN (select * from fn_Split_Up_Ids(@Id))
END
GO

HERE YOU GO :)
NOW CALL YOUR SP

SQL

EXEC spGetCountry '1,2'