While Loop in SQL Server

While loop in T-SQL

Here is a simple example of While loop

Declare @temp tinyint = 1

while (@temp < 11)

begin

print cast(@temp as varchar(2)) + ' X 2 = ' + cast(@temp * 2 as char)

set @temp += 1

end

The above code will be having the following output:

Output

1 X 2 = 2

2 X 2 = 4

3 X 2 = 6

4 X 2 = 8

5 X 2 = 10

6 X 2 = 12

7 X 2 = 14

8 X 2 = 16

9 X 2 = 18

10 X 2 = 20

'int' is not a recognized CURSOR option.

This error occurs when a variable name is preceded by # instead of @. # is used to declare temporary table

declare #myi int

Should be replaced by

declare @myi int

Compound Operators in SQL Server 2008

Compound operators execute some operation and set an original value to the result of the operation

Declare @temp tinyint

Select @temp = 1

Set @temp += @temp

print @temp

The following is a list of compound operators

Operator	Link to more information	Action
+=	+= (Add EQUALS) (Transact-SQL)	Adds some amount to the original value and sets the original value to the result.
-=	-= (Subtract EQUALS) (Transact-SQL)	Subtracts some amount from the original value and sets the original value to the result.
*=	*= (Multiply EQUALS) (Transact-SQL)	Multiplies by an amount and sets the original value to the result.
/=	/= (Divide EQUALS) (Transact-SQL)	Divides by an amount and sets the original value to the result.
%=	%= (Modulo EQUALS) (Transact-SQL)	Divides by an amount and sets the original value to the modulo.
&=	&= (Bitwise AND EQUALS) (Transact-SQL)	Performs a bitwise AND and sets the original value to the result.
^=	^= (Bitwise Exclusive OR EQUALS) (Transact-SQL)	Performs a bitwise exclusive OR and sets the original value to the result.
=	= (Bitwise OR EQUALS) (Transact-SQL)	Performs a bitwise OR and sets the original value to the result.

Date and Time Data Types in SQL Server 2008

Some of the new data that are introduced in Microsoft SQL Server 2008 are :

• Date
• Time
• Datetime2
• Datetimeoffset

Here is a simple self-explanatory example:

declare @date date = getdate()

declare @time time = getdate()

declare @dt2 datetime2 = getdate()

declare @dt datetime = getdate()

declare @sdt smalldatetime = getdate()

print 'Date ' + cast(@date as char)

print 'Time ' + cast(@time as char)

print 'SmallDateTime ' + cast(@sdt as char)

print 'DateTime ' + cast(@dt as char)

print 'DateTime2 ' + cast(@dt2 as char)

Here is the output of the same:

Date 2009-09-04

Time 16:18:54.5730000

SmallDateTime Sep 4 2009 4:19PM

DateTime Sep 4 2009 4:18PM

DateTime2 2009-09-04 16:18:54.5730000

Modulo operator in SQL Server

Modulo provides the integer remainder after dividing the first numeric expression by the second one.

print 'remainder ' + cast(112 % 17 as char)