How to loop through a table

11 Apr 2007 8:54 PM

Michael

Hi,

I have the following table X

create table x
(
a int
)
;

insert x
values(1);
insert x
values(2);
insert x
values(3);

Is there a way to extract each a in table X to get the following print
result?

x is 1
x is 2
x is 3

Thanks a lot!!
Michael

11 Apr 2007 9:12 PM

David Portas

Show quote Hide quote

"Michael" <michae***@gmail.com> wrote in message
news:1176324884.807557.48050@n76g2000hsh.googlegroups.com...
> Hi,
>
> I have the following table X
>
> create table x
> (
> a int
> )
> ;
>
> insert x
> values(1);
> insert x
> values(2);
> insert x
> values(3);
>
> Is there a way to extract each a in table X to get the following print
> result?
>
> x is 1
> x is 2
> x is 3
>
>
> Thanks a lot!!
> Michael
>

Replied in .programming
Please do not multi-post.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

11 Apr 2007 9:13 PM

Greg D. Moore (Strider)

Show quote Hide quote

"Michael" <michae***@gmail.com> wrote in message
news:1176324884.807557.48050@n76g2000hsh.googlegroups.com...
> Hi,
>
> I have the following table X
>
> create table x
> (
> a int
> )
> ;
>
> insert x
> values(1);
> insert x
> values(2);
> insert x
> values(3);
>
> Is there a way to extract each a in table X to get the following print
> result?

There is, but why would you want to loop?

Simply do a select.

select 'x is ' + cast(a as char(2)) from x;

Show quoteHide quote

>
> x is 1
> x is 2
> x is 3
>
>
> Thanks a lot!!
> Michael
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

11 Apr 2007 11:57 PM

SQL Menace

Show quote Hide quote

On Apr 11, 4:54 pm, "Michael" <michae***@gmail.com> wrote:
> Hi,
>
> I have the following table X
>
> create table x
> (
> a int
> )
> ;
>
> insert x
> values(1);
> insert x
> values(2);
> insert x
> values(3);
>
> Is there a way to extract each a in table X to get the following print
> result?
>
> x is 1
> x is 2
> x is 3
>
> Thanks a lot!!
> Michael

select 'x is ' + convert(varchar(20),a)
from x
order by a

Denis the SQL Menace
http://sqlservercode.blogspot.com/

12 Apr 2007 1:06 PM

Michael

Show quote Hide quote

On Apr 11, 7:57 pm, "SQL Menace" <denis.g***@gmail.com> wrote:
> On Apr 11, 4:54 pm, "Michael" <michae***@gmail.com> wrote:
>
>
>
>
>
> > Hi,
>
> > I have the following table X
>
> > create table x
> > (
> > a int
> > )
> > ;
>
> > insert x
> > values(1);
> > insert x
> > values(2);
> > insert x
> > values(3);
>
> > Is there a way to extract each a in table X to get the following print
> > result?
>
> > x is 1
> > x is 2
> > x is 3
>
> > Thanks a lot!!
> > Michael
>
> select 'x is ' + convert(varchar(20),a)
> from x
> order by a
>
> Denis the SQL Menacehttp://sqlservercode.blogspot.com/- Hide quoted text -
>
> - Show quoted text -

Thanks a lot!! The reason why I want to loop through the table is that
I simplified the problem. I need to loop through the table to get
exactly what I want. Anyone could show me that?

Thanks!!
Michael

12 Apr 2007 1:26 PM

David Portas

On 12 Apr, 14:06, "Michael" <michae***@gmail.com> wrote:
>
> Thanks a lot!! The reason why I want to loop through the table is that
> I simplified the problem. I need to loop through the table to get
> exactly what I want. Anyone could show me that?
>
> Thanks!!
> Michael
>

That's an unwise assumption. In my experience it's untrue at least
99.99% of the time so do not assume that you will need to loop through
the table row by row unless you have had an expert opinion to that
effect. Maybe you consider you are an expert but the fact that you are
asking this question at all suggests that you should take a much more
cautious approach - for example you could post an accurate description
of the problem to see if anyone can suggest alternative solutions.

What you are asking for is called a cursor. Look up DECLARE CURSOR in
Books Online. Cursors can be very bad news in the wrong hands and the
stock advice is that you should avoid cursors and write set-based code
instead wherever possible.

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

12 Apr 2007 2:01 PM

Greg D. Moore (Strider)

Show quote Hide quote

"Michael" <michae***@gmail.com> wrote in message
news:1176383189.058521.159980@e65g2000hsc.googlegroups.com...
> On Apr 11, 7:57 pm, "SQL Menace" <denis.g***@gmail.com> wrote:
>> On Apr 11, 4:54 pm, "Michael" <michae***@gmail.com> wrote:
>>
>>
>>
>>
>>
>> > Hi,
>>
>> > I have the following table X
>>
>> > create table x
>> > (
>> > a int
>> > )
>> > ;
>>
>> > insert x
>> > values(1);
>> > insert x
>> > values(2);
>> > insert x
>> > values(3);
>>
>> > Is there a way to extract each a in table X to get the following print
>> > result?
>>
>> > x is 1
>> > x is 2
>> > x is 3
>>
>> > Thanks a lot!!
>> > Michael
>>
>> select 'x is ' + convert(varchar(20),a)
>> from x
>> order by a
>>
>> Denis the SQL Menacehttp://sqlservercode.blogspot.com/- Hide quoted
>> text -
>>
>> - Show quoted text -
>
> Thanks a lot!! The reason why I want to loop through the table is that
> I simplified the problem. I need to loop through the table to get
> exactly what I want. Anyone could show me that?

Perhaps you should post exactly what you want then. We can only answer the
questions posted.

GENERALLY, looping through a table should be and can be avoided at all
costs.

But, take a look at CURSORS in the Books Online in SQL Server.

>
> Thanks!!
> Michael
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Thread options