How to traverse a table despite of unique identifer

Question

Post reply

How to traverse a table despite of unique identifer

vamshi krishna-303410

Right there with Babe

Points: 790
More actions
August 1, 2007 at 7:06 am

#153722

Hi,
assume that we retrive some tuples from a table
select * from product_information
ID    Name      Description
1     Laptop    Includes all laptop related devices
2     Desktop   All desktop PC devices
  3     RAM        All RAM memory Modules
4     PCI         PCI Components
  5     Cable      All kind of Cables

assume that i have declared string variable
declare @group_items varchar(300)
i need to read the ID values in to the string @group_items like each ID separating with a comma (,)
select @group_items
the above select command will display following output.
1,2,3,4,5
unique identifer can be used for comparing but should not be updated or.... i need to do this with or without cursors, kindly help me to solve this.

~vamshi krishna~

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

vamshi krishna-303410 Right there with Babe Points: 790 More actions · Answer 1

Thank you friends, I found the solution

I need to make a temp_table for product information table.

declare @value int,@min int

declare @return varchar(300),

@name varchar(300)

set @return=''

select @value=max(id) from product_information

select @min-2=min(id) from product_information

WHILE @value <> @min-2

BEGIN

DECLARE rs cursor FOR

SELECT ID FROM dbo.Product_Information

WHERE ID = @value

OPEN rs

FETCH rs INTO @name

CLOSE rs

delete from product_information where id = @ value

SELECT @value=max(id) FROM dbo.Product_information

IF @return <> ''

SET @return = ',' + @return

SET @return = @name + @return

DEALLOCATE rs

END

select @return

~vamshi krishna~

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 2

Good lord, NO!!!! Vamshi, I know you're kinda new at this, but holy mackeral this is bad! And, if you didn't write it, you gotta give me the URL you got it from so I can go straighten someone out

This will do the trick for what you've requested...

DECLARE @Group_Items VARCHAR(300)

SELECT @Group_Items = ISNULL(@Group_Items+',','') + CAST(ID AS VARCHAR(10))

FROM Product_Information

ORDER BY ID

SELECT @Group_Items

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vamshi krishna-303410 Right there with Babe Points: 790 More actions · Answer 3

Thank you jeff.

Your solution is better and useful then mine, i wrote it by referring to a book

~vamshi krishna~

David Burrows SSC Guru Points: 65144 More actions · Answer 4

Good lord, NO!!!! Vamshi, I know you're kinda new at this, but holy mackeral this is bad! And, if you didn't write it, you gotta give me the URL you got it from so I can go straighten someone out

WOW!! Jeff

Even I felt the wrath of God smite me on that one

Far away is close at hand in the images of elsewhere.
Anon.

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 5

I'm sorry Vamshi... I didn't mean to sound so outragous about what you wrote. It's outstanding that you're diving into a book (although you might want to get a different book based on what I've seen) and posting your solutions and I hope I haven't turned you off to either. My outrage was because I just got off a long stint of arguing with some other folks about how it's better to spend the time to find a correct setbased solution than it is to just settle for some form of loop. Granted, there are rare occasions when there simply is no other way than to use a loop, but people give up way to quickly.

I was like you and a million other folks when I first started in SQL Server... I was used to doing things in a row-by-row fashion and using loops which are very powerful tools in other languages. But I had a great Mentor that got me started... he told me that if I wrote a cursor for any of the problems he gave me, he'd stop mentoring.

SQL Server is unlike many other SQL RDBMS's... it works best if you can avoid loops and cursors in most every case. Like I said, there are exceptions, but they should be considered the rare exception rather than the rule or even a frequent occurance.

The key to setbased code is that you must change your thinking just a bit. Instead of doing things row by row, think about what you need to do to a whole column. Yeah, I know... easier said than done because it is quite the paradigm shift for most folks. But, once you get the hang of it, you'll be able to write code runs in minutes instead of hours on the batch side and milliseconds instead of seconds on the GUI side. And, it won't take you any longer to write that fast code than someone who uses row-by-row methods.

Keep digging into that book, Vamshi. You're doing the right thing by trying to teach yourself. Just remember, if you think you need to use a loop, there's some good chance there's a better way. I'll keep an eye out for your posts and I'll try to be a bit more "patient" in my replies

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 6

WOW!! Jeff

Even I felt the wrath of God smite me on that one

Yeah, huh? It was kinda like a parent watching a young child with long hair lean over a lit gas stove... (s)he didn't do anything wrong 'cause (s)he just didn't know, but it's difficult to act casual about it instead of yelling an urgent warning and taking immediate action

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vamshi krishna-303410 Right there with Babe Points: 790 More actions · Answer 7

Hey jeff,

i am inspired with your words, i strongly agree with you that we have to write very optimised queries then trying for loops which consumes more processor time in batch environment even in multi environment., thank you for your accompaniment, and when i saw your answer i became aware that my solution was really poor in quality and costly in terms of processing and strong, i am using your simple three line query in my program not my cursor looped procedure.

~vamshi krishna~

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 8

Thanks, Vamshi... I really appreciate your feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)