All of Query was successfully tested and running well on Microsoft SQL Server 2008
Tampilkan posting dengan label sql while. Tampilkan semua posting
Tampilkan posting dengan label sql while. Tampilkan semua posting

Selasa, 28 Agustus 2012

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

Senin, 27 Agustus 2012

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

Minggu, 26 Agustus 2012

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