sql case when

SQL Server Case Statement:
CASE [expression]  
   WHEN [value | Boolean expression] THEN [return value] 
   ELSE [return value]
END

Example 1:
declare @Gender nvarchar(6)
set @Gender='M' select case when @Gender='M' then 'Male' else 'Female' End 'Gender'

#Results:  
Gender
Male

 Example 2:  
declare @Gender nvarchar(6)
set @Gender='F' select case when @Gender='M' then 'Male' else 'Female' End 'Gender'

#Results:
Gender
Female

Example 3:
select Num, case Num  
                 when 100 then 'A'  
                 when 90 then 'B'  
                 when 80 then 'C'  
                 when 70 then 'D'
                 when 60 then 'E'  
             end 'Grade' from (
     select (100)Num union
     select (90) union
     select (80) union  
     select (70) union
     select (60)
)v1

#Results:
Num Grade
60 E
70 D
80 C
90 B
100 A

insert into select from

Another method to insert data record into a table in addition on previous post,

We still use table ProductList.


select * from ProductList

If results:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'ProductList'.

Create table ProductList first, use query below.

CREATE TABLE ProductList
(
    ProductId nvarchar(6),
    ProductName nvarchar(50),
    UnitPrice float,
    Quantity int
)

insert into ProductList select 'CPU001','CPU','300',15

select * from ProductList

#Results:
ProductId    ProductName    UnitPrice    Quantity
CPU001       CPU            300          15

sql insert into

On SQL Server, to add data record into a table use INSERT INTO Statement.

INSERT INTO (FieldName1,FieldName2,FieldName3, ...)TableName VALUES (value1, value2, value3, ...)

Example 1:
We will create a table first, its name ProductList.

--Create Table ProductList
CREATE TABLE ProductList
(
    ProductId nvarchar(6),
    ProductName nvarchar(50),
    UnitPrice float,
    Quantity int
)

After ProductList created, remark above query, cause table name is unique on a database.

select * from ProductList

#Results:
ProductId    ProductName    UnitPrice    Quantity

Empty, cause we not yet inserted  anyhing into ProductList, now insert with statement below

insert into ProductList (ProductId,ProductName,UnitPrice,Quantity) values ('LCD001','LCD Monitor','150',5)

select * from ProductList

#Results:
ProductId    ProductName    UnitPrice    Quantity
LCD001       LCD Monitor    150          5

Example 2:
We will insert 1 record againt into ProductList, in some fields only.

insert into ProductList (ProductId,ProductName) values ('LCD002','LCD Monitor') 

select * from ProductList
#Results:
ProductId    ProductName    UnitPrice    Quantity
LCD001       LCD Monitor    150          5
LCD002       LCD Monitor    NULL         NULL

sql create table

On SQL Server, to create table use statement as follows:

CREATE TABLE TableName
(
    FieldName1 DataType,
    FieldName2 DataType,
    FieldName3 DataType,
    ...
)

Example 1:
Create table with name EmployeeList.

CREATE TABLE EmployeeList
(
    EmployeeId int,
    EmployeeName nvarchar(50),
    EmployeeAddr nvarchar(50),
    EmployeeDept nvarchar(30)
)

After run on query analyzer, here are
#Results:
Command(s) completed successfully

Hereafter, copy and paste on query analyzer script below:

Select * from EmployeeList

#Results:
EmployeeId    EmployeeName    EmployeeAddr    EmployeeDept

This Table is empty.

We can be fill EmployeeList table with data,  use INSERT INTO SQL SERVER Statement.

insert into EmployeeList (EmployeeId,EmployeeName,EmployeeAddr,EmployeeDept) values ('11111','John Nuxer','Jl Raya Bekasi Km 28','MIS Dept')

Yes, now 1 data inserted to EmployeeList table.
See with run this query

Select * from EmployeeList

#Results:
EmployeeId    EmployeeName    EmployeeAddr            EmployeeDept
11111         John Nuxer      Jl Raya Bekasi Km 28    MIS Dept

How to create table with another way?  or Is there any way auto create table on SQL Server?
Yes, here.

sql round

SQL Server ROUND() Function used to get round a numeric field to number of decimals specified.

Statement:
SELECT ROUND(ColumnName,decimals) FROM TableName
where decimals is a number of decimals to be setup.

Example 1:
select ROUND(ItemPrice,0)ItemPrice from (
    select (15.424)ItemPrice union
    select (20.685) union
    select (37.273) union
    select (43.556) union
    select (59.317)
)v0

#Results:
ItemPrice
15.000
21.000
37.000
44.000
59.000

Example 2:
like Example 1 with decimal change to 1

select ROUND(ItemPrice,1)ItemPrice from (
    select (15.424)ItemPrice union
    select (20.685) union
    select (37.273) union
    select (43.556) union
    select (59.317)
)v0

#Results:
ItemPrice
15.400
20.700
37.300
43.600
59.300

Example 3:
like Example 2 with decimal change to 2

select ROUND(ItemPrice,2)ItemPrice from (
    select (15.424)ItemPrice union
    select (20.685) union
    select (37.273) union
    select (43.556) union
    select (59.317)
)v0

#Results:
ItemPrice
15.420
20.690
37.270
43.560
59.320

sql between

BETWEEN SQL Server operator use to get a range of data between two values.

SELECT ColumnName(s) FROM TableName WHERE ColumnName BETWEEN value1 AND value2.

Example 1:
select InvoiceNo from (
    select ('YAN/08/12/001')InvoiceNo,('2012/08/20 09:43:29.993')InvoiceDate union
    select ('YAN/08/12/002'),('2012/08/21 09:44:29.993') union
    select ('YAN/08/12/003'),('2012/08/22 09:45:29.993') union
    select ('YAN/08/12/004'),('2012/08/23 13:46:29.993') union
    select ('YAN/08/12/005'),('2012/08/24 09:47:29.993')
)v0 where InvoiceDate BETWEEN ('2012/08/21') AND ('2012/08/23')

#Results:
InvoiceNo
YAN/08/12/002
YAN/08/12/003

In fact there are 3 invoice for that period, but on above query result only 2. Modif above query like below (Example 2) to get actual results.

 Example 2:
select InvoiceNo from (
    select ('YAN/08/12/001')InvoiceNo,('2012/08/20 09:43:29.993')InvoiceDate union
    select ('YAN/08/12/002'),('2012/08/21 09:44:29.993') union
    select ('YAN/08/12/003'),('2012/08/22 09:45:29.993') union
    select ('YAN/08/12/004'),('2012/08/23 13:46:29.993') union
    select ('YAN/08/12/005'),('2012/08/24 09:47:29.993')
)v0 where CONVERT(DATE,InvoiceDate BETWEEN ('2012/08/21') AND ('2012/08/23')

#Results:
InvoiceNo
YAN/08/12/002
YAN/08/12/003
YAN/08/12/004

sql like

Operator LIKE on SQL Server used in a WHERE clause to search for a specified pattern in a column.

SELECT ColumnName(s) FROM TableName WHERE ColumnName LIKE pattern

Example 1:
select StudentName from (
    select ('Wardana Putra')StudentName union
    select ('Raihana Pratiwi') union
    select ('Juliana Eka Putri') union
    select ('Joko Susanto') union
    select ('Sugeng Joko Utomo')
)v0 where StudentName like 'J%'

#Results:
StudentName
Juliana Eka Putri
Joko Susanto

Example 2:
Still use Example 1 with small modification.
select StudentName from (
    select ('Wardana Putra')StudentName union
    select ('Raihana Pratiwi') union
    select ('Juliana Eka Putri') union
    select ('Joko Susanto') union
    select ('Sugeng Joko Utomo')
)v0 where StudentName like 'i%'

#Results:
StudentName
Juliana Eka Putri
Raihana Pratiwi

Example 3:
Still use Example 1 with small modification.
select StudentName from (
    select ('Wardana Putra')StudentName union
    select ('Raihana Pratiwi') union
    select ('Juliana Eka Putri') union
    select ('Joko Susanto') union
    select ('Sugeng Joko Utomo')
)v0 where StudentName like '%ana%'

#Results:
StudentName
Juliana Eka Putri
Raihana Pratiwi
Wardana Putra

sql sort order

SQL Server Order by Statement
SELECT ColumnName(s) FROM TableName Order by ColumnName Asc|Desc

Example 1:
--ascending order
select ('Rudi')FootBallTeam union
select ('Anton') union
select ('Budi') union
select ('Cahya') union
select ('Yahya') Order by FootBallTeam Asc

#Results:
FootBallTeam
Anton
Budi
Cahya
Rudi
Yahya

Example 2:

--descending order

select ('Rudi')FootBallTeam union
select ('Anton') union
select ('Budi') union
select ('Cahya') union
select ('Yahya') Order by FootBallTeam Desc

#Results:
FootBallTeam
Yahya
Rudi
Cahya
Budi
Anton

sql server group by

SQL Server Group by Statement
SELECT ColumnName, aggregate_function(ColumnName) FROM TableName
WHERE ColumnName operator value GROUP BY ColumnName
HAVING aggregate_function(ColumnName) operator value

Group by Statemet often used in conjunction with a having clause statement.

Example 1:
select Catagory from (
    select ('Stork')AnimalName,('Bird')Catagory union
    select ('Pigeon'),('Bird') union
    select ('Hawk'),('Bird') union
    select ('Shark'),('Fish') union
    select ('Dolphin'),('Fish')
)v0

#Results:
Catagory
Fish
Bird
Bird
Fish
Bird

Example 2:
Still use query on Example 1, with added group by statement.
 select Catagory from (
    select ('Stork')AnimalName,('Bird')Catagory union
    select ('Pigeon'),('Bird') union
    select ('Hawk'),('Bird') union
    select ('Shark'),('Fish') union
    select ('Dolphin'),('Fish')
)v0 group by Catagory

#Results:
Catagory
Bird
Fish

Example 3:
Still use query on Example 2, with added SQL Server COUNT() Function to get Count of Similiar Catagory.
select Catagory, COUNT(Catagory)NumOfCatagory from (
    select ('Stork')AnimalName,('Bird')Catagory union
    select ('Pigeon'),('Bird') union
    select ('Hawk'),('Bird') union
    select ('Shark'),('Fish') union
    select ('Dolphin'),('Fish')
)v0 group by Catagory

#Results:
Catagory    NumOfCatagory
Bird        3
Fish        2

having in sql

SQL Server Having Clause Statement:
SELECT ColumnName, aggregate_function(ColumnName) FROM TableName
WHERE ColumnName operator value GROUP BY ColumnName
HAVING aggregate_function(ColumnName) operator valu

Example 1:
 select CustName,ProductName,SUM(Qty)Quantity from (
    select ('Buana Jaya')CustName,('Tubex')ProductName,(10)Qty union
    select ('Citra Makmur'),('Tubex'),(15) union
    select ('Citra Makmur'),('Dengue'),(5) union
    select ('Buana Jaya'),('Tubex'),(5) union
    select ('Citra Makmur'),('Tubex'),(25)
)v0 group by CustName,ProductName having CustName='Citra Makmur'

#Results:
CustomerName    ProductName    Quantity
Citra Makmur    Dengue         5
Citra Makmur    Tubex          40

On Example 1, query can modified like show on Example 2 to make same result.

Example 2:
select CustName,ProductName,SUM(Qty)Quantity from (
    select ('Buana Jaya')CustName,('Tubex')ProductName,(10)Qty union
    select ('Citra Makmur'),('Tubex'),(15) union
    select ('Citra Makmur'),('Dengue'),(5) union
    select ('Buana Jaya'),('Tubex'),(5) union
    select ('Citra Makmur'),('Tubex'),(25)
)v0 where CustName='Citra Makmur' group by CustName,ProductName

#Results:
CustomerName    ProductName    Quantity
Citra Makmur    Dengue         5
Citra Makmur    Tubex          40

Notes:
Example 1 use combine group by having clause sql server statement while
Example 2 use combine where group by sql server statement

select top sql

SQL Server Select Top Statement:
SELECT TOP number | number PERCENT ColumnName(s) FROM TableName

Example 1:

select TOP 1 * from (
    select ('Umar')PersonName union
    select ('Zubair') union
    select ('Ali') union
    select ('Muhammad') union
    select ('Bilal')
)v0 Order by PersonName Asc

#Results:
PersonName
Ali

Example 2:
select TOP 1 * from (
    select ('Umar')PersonName union
    select ('Zubair') union
    select ('Ali') union
    select ('Muhammad') union
    select ('Bilal')
)v1 Order by PersonName Desc

#Results:
PersonName
Zubair

Example 3:
select TOP 3 * from (
    select ('Umar')PersonName union
    select ('Zubair') union
    select ('Ali') union
    select ('Muhammad') union
    select ('Bilal')
)v2 Order by PersonName Asc


#Results:
PersonName
Ali
Bilal
Muhammad

Example 4:
select TOP 3 * from (
    select ('Umar')PersonName union
    select ('Zubair') union
    select ('Ali') union
    select ('Muhammad') union
    select ('Bilal')
)v2 Order by PersonName Desc


PersonName 
Zubair
Umar
Muhammad

Example 5:
select TOP 30 PERCENT * from (
    select ('Umar')PersonName union
    select ('Zubair') union
    select ('Ali') union
    select ('Muhammad') union
    select ('Bilal')
)v2 Order by PersonName Desc

#Results:
PersonName 
Zubair
Umar

sql select count

SQL Server COUNT() function use to get number of rows that matches a specified criteria.

Example 1:
select COUNT(ItemName)NumOfItem from (
    select ('pencil')ItemName,('P0001')ItemCode,(10)Qty union
    select ('pencil'),('P0001'),(20) union
    select ('pencil'),('P0001'),(30) union
    select ('pen'),('P0002'),(15) union
    select ('pen'),('P0002'),(25) union
    select ('pen'),('P0002'),(35)
)v0

#Results:
NumOfItem
6

Example 2:
select COUNT(DISTINCT ItemName)NumOfItem from (
    select ('pencil')ItemName,('P0001')ItemCode,(10)Qty union
    select ('pencil'),('P0001'),(20) union
    select ('pencil'),('P0001'),(30) union
    select ('pen'),('P0002'),(15) union
    select ('pen'),('P0002'),(25) union
    select ('pen'),('P0002'),(35)
)v1

#Results:
NumOfItem
2

Example 3:
See following examples and compare with previous query:


select COUNT(DISTINCT ItemName)NumOfItem from (
    select ('pencil')ItemName,('P0001')ItemCode,(10)Qty union
    select ('pencil'),('P0001'),(20) union
    select ('pencil'),('P0001'),(30) union
    select ('pen'),('P0002'),(15) union
    select ('pen'),('P0002'),(25) union
    select ('pen'),('P0002'),(35) union

    select (null),('P0003'),1
)v2

#Results:
NumOfItem
2


Notes:
SQL Server DISTINCT sintax added to get number of unique item name
Results Example 2 and 3  same couse NULL values will not be counted

convert month name to number sql

Use Combine DATEPART() and CAST() SQL Server Function.

For example:
SELECT DATEPART(mm,CAST('February'+ ' 1900' AS DATETIME))NumOfThisMonth

#Results:
NumOfThisMonth
2

select OrdNum,DATEPART(mm,CAST(MonthNames+ ' 1900' AS DATETIME))NumOfMonth from (
    select (1)OrdNum,'January'MonthNames union
    select (2),'February' union
    select (3),'March' union
    select (4),'April' union
    select (5),'May' union
    select (6),'June' union
    select (7),'July' union
    select (8),'August' union
    select (9),'September' union
    select (10),'October' union
    select (11),'November' union
    select (12),'December'
)v0

Compare with this another way:
SELECT CHARINDEX('OCT','XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')/3 AS monthNumber

#Results:
monthNumber
10

select OrdNum,DATEPART(mm,CAST(MonthNames+ ' 1900' AS DATETIME))NumOfMonth,
CHARINDEX(MonthNames,'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')/3 AS monthNumber
from (
    select (1)OrdNum,'JAN'MonthNames union
    select (2),'FEB' union
    select (3),'MAR' union
    select (4),'APR' union
    select (5),'MAY' union
    select (6),'JUN' union
    select (7),'JUL' union
    select (8),'AUG' union
    select (9),'SEP' union
    select (10),'OCT' union
    select (11),'NOV' union
    select (12),'DEC'
)v1

OrdNum    NumOfMonth    monthNumber
1         1             1
2         2             2
3         3             3
4         4             4
5         5             5
6         6             6
7         7             7
8         8             8
9         9             9
10        10            10
11        11            11
12        12            12

average in sql

AVG() on SQL Server Function use to get average value of a numeric column.

select (1)Num,('Product One')ItemName,(2500000)ItemPrice Union
select (2),('Product Two'),(3500000) Union
select (3),('Product Three'),(4500000) Union
select (4),('Product Four'),(6500000) Union
select (5),('Product Five'),(7500000)

#Results:
Num  ItemName       ItemPrice
1    Product One    2500000
2    Product Two    3500000
3    Product Three  4500000
4    Product Four   6500000
5    Product Five   7500000

select AVG(ItemPrice)AvgItemPrice from (
   select (1)Num,('Product One')ItemName,(2500000)ItemPrice Union
   select (2),('Product Two'),(3500000) Union
   select (3),('Product Three'),(4500000) Union
   select (4),('Product Four'),(6500000) Union
   select (5),('Product Five'),(7500000)
)v0

#Results:
AvgItemPrice
4900000

sql datepart

DATEPART() SQL Server function Statement:

DATEPART(datepart,date)

declare @ThisDate datetime set @ThisDate='2012-08-30 08:45:08.717'  
select ('yy')DATEPARTInit,('year')UseForShow,DATEPART(yy,@ThisDate)ShowData Union
select ('qq'),('quarter'),DATEPART(qq,@ThisDate) Union
select ('mm'),('month'),DATEPART(mm,@ThisDate) Union
select ('dy'),('dayofyear'),DATEPART(dy,@ThisDate) Union
select ('dd'),('day'),DATEPART(dd,@ThisDate) Union
select ('wk'),('week'),DATEPART(wk,@ThisDate) Union
select ('dw'),('weekday'),DATEPART(dw,@ThisDate) Union
select ('hh'),('hour'),DATEPART(hh,@ThisDate) Union
select ('mi'),('minute'),DATEPART(mi,@ThisDate) Union
select ('ss'),('second'),DATEPART(ss,@ThisDate) Union
select ('ms'),('second'),DATEPART(ms,@ThisDate) --union
--select ('mcs'),('microsecond'),DATEPART(mcs,@ThisDate) union
--select ('ns'),('nanosecond'),DATEPART(ns,@ThisDate)

#Results:
DatePartInit    UseForShow ShowData
dd              day        30
dw              weekday    5
dy              dayofyear  243
hh              hour       8
mi              minute     45
mm              month      8
ms              second     717
qq              quarter    3
ss              second     8
wk              week       35
yy              year       2012

sql date convert

SQL Server Convert Date Time Function Statement:
Convert(data_type(length),expression,style)

declare @CurrDate datetime
set @CurrDate='2012-08-30 07:54:52.293'

select (100)ShowStyle,Convert(nvarchar,@CurrDate,100)ShowOutput Union
select (101),Convert(nvarchar,@CurrDate,101) Union
select (102),Convert(nvarchar,@CurrDate,102) Union
select (103),Convert(nvarchar,@CurrDate,103) Union
select (104),Convert(nvarchar,@CurrDate,104) Union
select (105),Convert(nvarchar,@CurrDate,105) Union
select (106),Convert(nvarchar,@CurrDate,106) Union
select (107),Convert(nvarchar,@CurrDate,107) Union
select (108),Convert(nvarchar,@CurrDate,108) Union
select (109),Convert(nvarchar,@CurrDate,109) Union
select (110),Convert(nvarchar,@CurrDate,110) Union
select (111),Convert(nvarchar,@CurrDate,111) Union
select (112),Convert(nvarchar,@CurrDate,112) Union
select (113),Convert(nvarchar,@CurrDate,113) Union
select (114),Convert(nvarchar,@CurrDate,114) Union
select (120),Convert(nvarchar,@CurrDate,120) Union
select (121),Convert(nvarchar,@CurrDate,121) Union
select (126),Convert(nvarchar,@CurrDate,126) Union
select (130),Convert(nvarchar,@CurrDate,130) Union
select (131),Convert(nvarchar,@CurrDate,131)

#Results:
ShowStyle    ShowOutput
100          Aug 30 2012  7:54AM
101          08/30/2012
102          2012.08.30
103          30/08/2012
104          30.08.2012
105          30-08-2012
106          30 Aug 2012
107          Aug 30, 2012
108          07:54:52
109          Aug 30 2012  7:54:52:293AM
110          08-30-2012
111          2012/08/30
112          20120830
113          30 Aug 2012 07:54:52:293
114          07:54:52:293
120          2012-08-30 07:54:52
121          2012-08-30 07:54:52.293
126          2012-08-30T07:54:52.293
130          13 شوال 1433  7:54:52:293AM
131          13/10/1433  7:54:52:293AM

right in sql

--right sql server function for static position
select RIGHT(MyAnimals,10)AnimalName,LEFT(MyAnimals, CHARINDEX(' ', MyAnimals)-1)qty from (
    select ('5 chickens A')MyAnimals union
    select ('15 chickens B') union
    select ('25 chickens C') union
    select ('35 chickens D') union
    select ('45 chickens E')
)v0

#Results:
AnimalName    qty
chickens B     15
chickens C     25
chickens D     35
chickens E     45
chickens A      5

--right sql server function for dinamic position
select MyAnimals,quantity,RIGHT(MyAnimals,LEN(MyAnimals)-LEN(quantity))AnimalName from (
    select MyAnimals,LEFT(MyAnimals, CHARINDEX(' ',MyAnimals)-1)quantity from (
        select ('5 chickens')MyAnimals union
        select ('14 goats') union
        select ('7 chickens') union
        select ('10 cows') union
        select ('500 ducks')
    )v2
)v3

#Results:
MyAnimals    quantity    AnimalName
10 cows            10    cows
14 goats           14    goats
5 chickens          5    chickens
500 ducks         500    ducks
7 chickens          7    chickens

sql substring

Substring function on sql server is a function like mid on asp, to get one or more characters in the middle of a set of characters.

--substring sql server function for static position
select notes,substring(notes,11,3)qty from (
    select ('bicycle A 285 unit')notes union
    select ('bicycle B 153 unit') union
    select ('bicycle C 120 unit') union
    select ('bicycle D 569 unit') union
    select ('bicycle E 587 unit')
)v1

#Results:
notes                 qty
bicycle A 285 unit    285
bicycle B 153 unit    153
bicycle C 120 unit    120
bicycle D 569 unit    569
bicycle E 587 unit    587

--substring sql server function for dinamic position
select notes,substring(notes, CHARINDEX(' ',notes)+1,3)qty from (
    select ('bicycle 285 unit')notes union
    select ('motorcycle 153 unit') union
    select ('cabriolet 120 unit') union
    select ('motor 406 unit') union
    select ('pedicab 587 unit')
)v1 

notes               qty
bicycle 285 unit    285
cabriolet 120 unit  120
motor 406 unit      406
motorcycle 153 unit 153
pedicab 587 unit    587

left sql function

-- left sql server function for static position
select CatNo,LEFT(CatNo,8)NewCatNo from (
    select ('MA 30012~100')CatNo union
    select ('MB 30542~20012') union
    select ('MB 30031~321') union
    select ('CA 30035~356487') union
    select ('CB 30041~310205') union
    select ('CC 31112~333')
)v0

#Results:
CatNo              NewCatNo
CA 30035~356487    CA 30035
CB 30041~310205    CB 30041
CC 31112~333       CC 31112
MA 30012~100       MA 30012
MB 30031~321       MB 30031
MB 30542~20012     MB 30542

-- sql server left function for dinamic position
select CatNo,LEFT(CatNo,CHARINDEX('~',CatNo)-1)NewCatNo from (
    select ('MA 30012567~100')CatNo union
    select ('MB 3054231~20012') union
    select ('MB 30031~321') union
    select ('CA 300352345~356487') union
    select ('CB 30041056879~310205') union
    select ('CC 311~333')
)v0

CatNo                    NewCatNo
CA 300352345~356487      CA 300352345
CB 30041056879~310205    CB 30041056879
CC 311~333               CC 311
MA 30012567~100          MA 30012567
MB 30031~321             MB 30031
MB 3054231~20012         MB 3054231

break on sql

declare @loop int
declare @spell nvarchar(15)
declare @query nvarchar(300)
set @loop=10
set @spell=''
set @query=''

WHILE not @loop =20
    Begin
        if @loop=11
            set @spell='''Eleven'''
        else if @loop=12
            set @spell='''Twelve'''
        else if @loop=13
            set @spell='''Thirteen'''
        else if @loop=14
            set @spell='''Fourteen'''
        else if @loop=15
            set @spell='''Fifteen'''
        else if @loop=16
            set @spell='''Sixteen'''
        else if @loop=17
            BEGIN -- Starting from this script down will never executed
                set @spell='''Seventeen'''
                BREAK
            END
        else if @loop=18
                set @spell='''Eighteen'''
        else if @loop=19
            set @spell='''Nineteen'''
        else
            set @spell='''Twenty'''
   
        set @loop=@loop+1
      
        if @query=''
            set @query='select '+convert(nvarchar,@loop)+'num,'+@spell+'spell'
        else
            set @query=@query+' union '+'select '+convert(nvarchar,@loop)+','+@spell
    End
    exec (@query)

#Results:
num    spell
11     Twenty
12     Eleven
13     Twelve
14     Thirteen
15     Fourteen
16     Fifteen
17     Sixteen

sql if else

declare @loop int
declare @spell nvarchar(15)
declare @qry nvarchar(300)
set @loop=0
set @spell=''
set @qry=''

WHILE not @loop =10
    Begin
        set @loop=@loop+1
        if @loop=1
            set @spell='''One'''
        else if @loop=2
            set @spell='''Two'''
        else if @loop=3
            set @spell='''Three'''
        else if @loop=4
            set @spell='''Four'''
        else if @loop=5
            set @spell='''Five'''
        else if @loop=6
            set @spell='''Six'''
        else if @loop=7
            set @spell='''Seven'''
        else if @loop=8
            set @spell='''Eight'''
        else if @loop=9
            set @spell='''Nine'''
        else
            set @spell='''Ten'''
       
        if @qry=''
            set @qry='select '+convert(nvarchar,@loop)+'num,'+@spell+'spell'
        else
            set @qry=@qry+' union '+'select '+convert(nvarchar,@loop)+','+@spell
    End
    exec (@qry)

#Results:
num    spell
1      One
2      Two
3      Three
4      Four
5      Five
6      Six
7      Seven
8      Eight
9      Nine
10     Ten

sql exec

Exec sintax on sql server usually use to execute parameter

see example below:
select ('soemaryanto')usr_name,('admin')access_sttus,('active')active_status

#Results:
usr_name       access_sttus    active_status
soemaryanto    admin           active

declare @qry nvarchar(100)
set @qry=('select (''soemaryanto'')usr_name,(''admin'')access_sttus,(''active'')active_status')
select (@qry)text_qry


exec (@qry)

#Results:
text_qry
select ('soemaryanto')usr_name,('admin')access_sttus,('active')active_status

usr_name       access_sttus    active_status
soemaryanto    admin           active

another example exec sql server statement
declare @count int
declare @qry nvarchar(100)
declare @qryBuffer nvarchar(200)
set @count=0
set @qry=''
set @qryBuffer=''

WHILE not @count =10
BEGIN
    set @count=@count+1
    if @qryBuffer=''
        set @qryBuffer=('select ('+convert(nvarchar,@count)+')AutoNum')
    else
        set @qryBuffer=@qryBuffer+' union '+('select ('+convert(nvarchar,@count)+')')
END
exec (@qryBuffer)

#Results:
AutoNum
1
2
3
4
5
6
7
8
9
10

day name in sql

SELECT DATENAME(dw, 1)NameofThisDay

#Results:
NameofThisDay
Tuesday

SELECT DATENAME(dw, '2012-08-27 11:12:38.803')NameofThisDay

#Results:
NameofThisDay
Monday

Another example with complex case to get days a week with while loop sql server statement

declare @start int,@end int
declare @qry nvarchar(100)
declare @daysAWeek nvarchar(1000)
set @start=1
set @end=8
set @daysAWeek=''
while not (@start=@end)
    begin
        if @start=@end-1
            set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')dayNumber,
             DateName(dw,'+convert(nvarchar,@start)+')dayName')qry)
        else
            set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')dayNumber,
             DateName(dw,'+convert(nvarchar,@start)+')dayName union ')qry)

        set @daysAWeek=@daysAWeek+@qry       
        set @start=@start+1
    end
    exec (@daysAWeek)

#Results:
dayNumber    dayName
1            Tuesday
2            Wednesday
3            Thursday
4            Friday
5            Saturday
6            Sunday
7            Monday

sql month name

declare @start int,@end int
declare @qry nvarchar(100)
declare @summary nvarchar(1200)
set @start=1
set @end=13
set @summary=''
while not (@start=@end)
    begin
        if @start=@end-1
            set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')monthNumber,            DateName(mm,DATEADD(mm,'+convert(nvarchar,@start)+',-1))monthName')qry)
        else
            set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')monthNumber,            DateName(mm,DATEADD(mm,'+convert(nvarchar,@start)+',-1))monthName 
            union ')qry)

        set @summary=@summary+@qry       
        set @start=@start+1
    end
    exec (@summary)

#Results:
monthNumber    monthName
1              January
2              February
3              March
4              April
5              May
6              June
7              July
8              August
9              September
10             October
11             November
12             December

select DateName(mm,'2012-08-27 10:10:17.327')currMonthName

#Results:
currMonthName
August

sql loop

DECLARE @count int
SELECT @count =11

--Loop sql statement start here
WHILE @count > 0
    BEGIN
        SELECT @count=@count-1
        IF @count=0
            --BEGIN
            --    BREAK
            --END

            IF @count=11
                BEGIN
                CONTINUE
            END
        --Show @count on Query Screen
        PRINT @count
    END 
--End of Loop

#Results:
10
9
8
7
6
5
4
3
2
1
0

group by

Group by statement on sql server often use to show some field with one or more of them contain number which will be summed.

We still use example previous post, with small modification.

select product_name,SUM(qty)qty from (
    select 'book notes'product_name,(4)qty union
    select 'pencil',(15) union
    select 'book notes',(3) union
    select 'pencil',(10)
)view1 group by product_name order by product_name 

#Results:
product_name    qty
book notes        7
pencil           25 

view sql

Views that i mean is we can use views method without creat views.

For example:
select product_name,qty from (
    select 'book notes'product_name,(4)qty union
    select 'pencil',(15) union
    select 'book notes',(3) union
    select 'pencil',(10)
)view1 where product_name='pencil' order by product_name

#Results:
product_name    qty
pencil          15
pencil          10

sql comment

When developing, we can always use remark code, a code will be ignored when execute. There are two script comment on sql server, comment line and block comment.

See example below:

this is
sql server
comment line
select 'computer'product_name,(5)qty

#Results:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'is'. 

--this is
--sql server
--comment line
select 'computer'product_name,(5)qty

#Results:
product_name    qty
computer          5


/*this is
sql server
block comment*/
select 'computer'product_name,(5)qty


#Results:
product_name    qty
computer          5