about cursors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

about cursors

Author

Topic

kuku
Starting Member

14 Posts

Posted - 2011-03-17 : 12:11:16

hi guys!!can u help me please??how can i select list of ID separated by commas?
for example (1,2,3,5)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-17 : 12:15:33

What does this have to do with cursors?

SELECT * FROM yourTable WHERE id in (1, 2, 3, 5)

Don't need to post the same question twice by the way.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-17 : 12:17:24

If you provide the DDL and expected output, I can give you a more customized answer

SELECT DISTINCT
STUFF((SELECT ',' + s2.CODE
FROM (select '1' as Code union select '2' union select '3' union select '5') AS s2

FOR XML PATH('')
), 1, 1, ''
) AS CODES
FROM (select '1' as Code union select '2' union select '3' union select '5') AS s1

Jim

Everyday I learn something that somebody else already knew

kuku
Starting Member

14 Posts

Posted - 2011-03-17 : 12:29:20

thanks jimf,but i need to read my table's IDs and make the string.
for example
ID Name
1 Alen
2 Sue
4 Rex

i need (1,2,4) string

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-17 : 12:33:56

DECLARE @Table Table (id int,name varchar(10))

INSERT INTO @Table
Select 1,'Alen' UNION
Select 2,'Sue' UNION
Select 4,'Rex'

SELECT DISTINCT
STUFF((SELECT ',' + convert(varchar(10),s2.ID)
FROM @table AS s2

FOR XML PATH('')
), 1, 1, ''
) AS IDs
FROM @table AS s1

Jim

Everyday I learn something that somebody else already knew

kuku
Starting Member

14 Posts

Posted - 2011-03-17 : 12:45:29

thanks again but can you write this with explicit cursor?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-17 : 12:48:56

No. I've never needed to use cursors and in SQL Server you should rarely need to. Why do you have to do this with cursors?

Jim

Everyday I learn something that somebody else already knew

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-17 : 15:18:16

I don't remember whose signature is "Cursors are useful when you don't know SQL". To great extent we perfer Set based approach and not cursors.

Still for some reason if you want to use cursor ( may be for learning then you can do this way )

Declare a varible and set it to '' value (empty value)
Ex : Declare @FinVal varchar(max) = ''

Declare curson
Open the cursor
Fetch next row.
Check the value of @@FETCH_STATUS = 0

Assign the value of the variable (variable holding the column value in cursor ) to main variable holding all retrived values.
Ex: set @FinVal = @FinVal + @Cursor variable +','

Fetch next row
Close the cursor
deallocate the cursor

kuku
Starting Member

14 Posts

Posted - 2011-03-21 : 03:40:31

thanks a lot pk_bohra,but can you write code in details,plz

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-21 : 09:59:08

<<
I don't remember whose signature is "Cursors are useful when you don't know SQL".
>>

Nigel Rivett

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-03-23 : 07:28:55

quote:
Originally posted by kuku

thanks a lot pk_bohra,but can you write code in details,plz

This statement tells us that this is homework, and we will NOT do that for you. You have been given examples in GREAT DETAIL on how to (not) use a cursor.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources