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