For loops in Tsql

Can somebody please tell me how can i write a tsql statement in sql server 2000.

Same time how can i get the last digit of a inteager variable through tsql .What i want is to write 'right(intVariable,4) which is in vb .I want that in sql server 2000

Thank you

The best way to write TSql is to use Query Analyzer.

To get the last digit of an integer variable try: Right( Convert(varchar(10), intVariable ), 1 ) That will turn the int into a string, then get the last digit.

Since your subject has For Loop, but you haven't asked about For Loops, I'll just throw it in. TSql has While() instead of For. Declare a variable for your counter, use the While, and be sure to increment the counter.

Declare @.counter int

Set @.counter = 1

While @.counter < 10

Begin

Do something significant

Set @.counter = @.counter + 1

End

|||

Hi rohanfernando, I moved your question over to the Transact-SQL forum. The folks over here should be able to help you with your question.

Cheers,

JJustice [MSFT]

|||The easiest way to get the last digit of an integer is to get the 'mod 10' of it. Like this:

select 234 % 10 --gives 4
select 12098523 % 10 --gives 3

Rob

for auto question

Hi,
The following TSQL command ;
select * from tbldata for xml auto, elements
brings back the xml form of all the data records.
Hence, for a table with multiple records on the output is;
<tblrecord>
<text>Hi</text>
</tblrecord>
<tblrecord>
<text>Goodbye</text>
</tblrecord>
Is there any way (with the transact SQL command) to get it to put a top
level element around the data - so it can be immediately viewable in I.E.
i.e. To put the <records> tags into the start and end.
<records>
<tblrecord>
<text>Hi</text>
</tblrecord>
<tblrecord>
<text>Goodbye</text>
</tblrecord>
</records>
Many thanks in advance,
Mike.
No - the FOR XML clause is designed to return an XML fragment, not a
complete document. You'd have to use the SQLXML client-side technology to
add the root tag. Basically, the choices are to use an annotated schema
(with an "is-constant" root element), an XML template, the "xml root"
property of the ADO command object, the RootTag property of the ADO.NET
SqlXmlCommand object, or the root parameter in a SQLISAPI URL. Most of these
(apart from the ADO.NET approach) are available in the original release of
SQL Server 2000 and are discussed in the SQL Server Books Online. However,
you'd be best installing the latest SQLXML 3.0 release
(http://www.microsoft.com/downloads/d...33A9-CF10-4E22
-8004-477098A407AC&displaylang=en).
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"Mike G" <mickyg@.mickyg.com> wrote in message
news:%235WFDHMcEHA.2844@.TK2MSFTNGP12.phx.gbl...
Hi,
The following TSQL command ;
select * from tbldata for xml auto, elements
brings back the xml form of all the data records.
Hence, for a table with multiple records on the output is;
<tblrecord>
<text>Hi</text>
</tblrecord>
<tblrecord>
<text>Goodbye</text>
</tblrecord>
Is there any way (with the transact SQL command) to get it to put a top
level element around the data - so it can be immediately viewable in I.E.
i.e. To put the <records> tags into the start and end.
<records>
<tblrecord>
<text>Hi</text>
</tblrecord>
<tblrecord>
<text>Goodbye</text>
</tblrecord>
</records>
Many thanks in advance,
Mike.
|||Note that FOR XML in SQL Server 2005 is adding a ROOT directive. But for SQL
Server 2000, Graeme's answer is correct.
Best regards
Michael
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:e8XX9EXcEHA.1000@.TK2MSFTNGP12.phx.gbl...
> No - the FOR XML clause is designed to return an XML fragment, not a
> complete document. You'd have to use the SQLXML client-side technology to
> add the root tag. Basically, the choices are to use an annotated schema
> (with an "is-constant" root element), an XML template, the "xml root"
> property of the ADO command object, the RootTag property of the ADO.NET
> SqlXmlCommand object, or the root parameter in a SQLISAPI URL. Most of
> these
> (apart from the ADO.NET approach) are available in the original release of
> SQL Server 2000 and are discussed in the SQL Server Books Online. However,
> you'd be best installing the latest SQLXML 3.0 release
> (http://www.microsoft.com/downloads/d...33A9-CF10-4E22
> -8004-477098A407AC&displaylang=en).
> Hope that helps,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "Mike G" <mickyg@.mickyg.com> wrote in message
> news:%235WFDHMcEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> The following TSQL command ;
> select * from tbldata for xml auto, elements
> brings back the xml form of all the data records.
> Hence, for a table with multiple records on the output is;
> <tblrecord>
> <text>Hi</text>
> </tblrecord>
> <tblrecord>
> <text>Goodbye</text>
> </tblrecord>
> Is there any way (with the transact SQL command) to get it to put a top
> level element around the data - so it can be immediately viewable in I.E.
> i.e. To put the <records> tags into the start and end.
> <records>
> <tblrecord>
> <text>Hi</text>
> </tblrecord>
> <tblrecord>
> <text>Goodbye</text>
> </tblrecord>
> </records>
>
> Many thanks in advance,
> Mike.
>
>