Wihendro's Web Blog

Job vacancy as Software Engineer

2011-08-22T11:55:00.001+07:00

Software Engineer – IS Development (University)
Report to : IS Development Manager
Location : Syahdan Campus and JWC Campus

Purpose of Jobs:
- Ensure the application development process alignment by conducting the following activities:
- Ensure that the application has matched with user requirement
- Ensure that the application has been completed as scheduled by Senior System Analyst
- Monitor and enhance user’s satisfaction towards various IS Application Development
- Conduct coding according to the application design plan and ensure its timely completion
- Handle the overall application installation to user
- Provide user’s guide application for related user
- Maintain the application develop and conduct maintenance action needed

Requirements :
- Min. 3rd Year Students of Bachelor Degree in Computer Studies
- Max. age 30 year-old
- Have work experience in a relevant position or field, would be considered as an advantage
- Have knowledge of programming tools (ASP.net,C# )and Data Processing (SQL Server 2000/2005), would be considered

as an advantage

Send your CV to
IT Directorate Bina Nusantara
Kampus Syahdan, Gedung H Lt.2
Jl. K.H. Syahdan No.9 Palmerah, Jakarta Barat, Indonesia
or email to Cirylla@binus.edu dengan subject ISDEV Software Engineer - (your Name)

have a nice day

Wihendro
IS Dev Manager University & JWC
http://www.binus.ac.id

NTLDR is missing

2010-02-13T22:28:00.004+07:00

Just want to share tricks to take your windows back if you find

NTLDR is missing
ctrl+Alt+Del to restart

Read this article ( in Bahasa :D )

http://www.giest.org/microsoft/ntldr-is-missing.html
http://guntingbatukertas.com/sistem-operasi/windows/ntldr-missing-menangani-pesan-error-file-windows-yang-hilang/

have a nice day
Wihendro
BiNus University System Specialist
http://www.binus.ac.id

Kamen Rider Decade eps 22 to Final episode

2009-09-25T09:41:00.006+07:00

Kamen Rider Decade Eps 21 Subbed

2009-06-22T12:10:00.001+07:00

For all kamen rider fans, Here are Episode 21 which has been subbed!!

http://www.tvnihon.com/tracker/torrents-details.php?id=946

have a nice day

Kamen Rider Decade Eps 20 Subbed

2009-06-15T09:13:00.002+07:00

For all kamen rider fans, Here are Episode 20 which has been subbed!!

http://www.tvnihon.com/tracker/torrents-details.php?id=939

have a nice day

Kamen Rider Decade Eps 19 Subbed

2009-06-03T15:11:00.001+07:00

For all kamen rider fans, Here are Episode 19 which has been subbed!!

http://www.tvnihon.com/tracker/torrents-details.php?id=922

have a nice day

Kamen Rider Decade Eps 18 Subbed

2009-05-29T10:45:00.000+07:00

For all kamen rider fans, Here are Episode 18 which has been subbed!!

http://www.tvnihon.com/tracker/torrents-details.php?id=915

have a nice day

Kamen Rider Decade Eps 17 Subbed

2009-05-22T11:25:00.000+07:00

For all kamen rider fans, Here are Episode 17 which has been subbed!!

http://www.tvnihon.com/tracker/torrents-details.php?id=905

have a nice day

Kamen Rider Decade Eps 16 Subbed

2009-05-22T11:18:00.000+07:00

For all kamen rider fans, Here are Episode 16 which has been subbed!!

SD= http://www.tvnihon.com/tracker/torrents-details.php?id=895
HD= http://www.tvnihon.com/tracker/torrents-details.php?id=894

have a nice day

Kamen Rider Decade Eps 15 Subbed

2009-05-09T13:47:00.000+07:00

For all kamen rider fans, Here are Episode 15 which has been subbed!!

http://www.tvnihon.com/tracker/torrents-details.php?id=890

have a nice day

Kamen Rider Decade Eps 14 Subbed

2009-05-01T10:06:00.001+07:00

For all kamen rider fans, Here are Episode 14 which has been subbed!!

http://www.nyaatorrents.org/?page=torrentinfo&tid=61435

have a nice day

Kamen Rider Decade Eps 13 Subbed

2009-04-25T08:25:00.001+07:00

For all kamen rider fans, Here are Episode 13 which has been subbed!!

http://www.nyaatorrents.org/?page=torrentinfo&tid=60286

have a nice day

Kamen Rider Decade Eps 12 Subbed

2009-04-16T13:39:00.001+07:00

For all kamen rider fans, Here are Episode 12 which has been subbed!!

http://www.tvnihon.com/tracker/torrents-details.php?id=857

have a nice day

Kamen Rider Decade Eps 11 Subbed

2009-04-11T08:51:00.001+07:00

For all kamen rider fans, Here are Episode 11 which has been subbed!!

http://www.megaupload.com/?d=P4143UGD

or watch in website,
http://www.youtube.com/watch?v=NgGsW0IVhmo
http://www.youtube.com/watch?v=6pkdfqj_fHI&feature=related
http://www.youtube.com/watch?v=ZSrXVqSN8ho

have a nice day

Kamen Rider Decade Eps 10 Subbed

2009-04-05T10:03:00.000+07:00

For all kamen rider fans, Here are Episode 10 which has been subbed!!

http://tvnihon.com/tracker/torrents-details.php?id=840

have a nice day

Kamen Rider Decade Eps 09 Subbed

2009-03-29T07:36:00.000+07:00

For all kamen rider fans, Here are Episode 9 which has been subbed!!

http://www.tvnihon.com/tracker/torrents-details.php?id=826

have a nice day

Kamen Rider Decade Eps 08 Subbed

2009-03-22T08:27:00.001+07:00

For all kamen rider fans, Here are Episode 8 which has been subbed!!

http://www.tvnihon.com/tracker/torrents-details.php?id=812

have a nice day

Kamen Rider Decade Eps 07 Subbed

2009-03-14T00:08:00.000+07:00

For all kamen rider fans, Here are Episode 7 which has been subbed!!

http://www.tvnihon.com/tracker/torrents-details.php?id=794

have a nice day

Kamen Rider Decade Eps 06 Subbed

2009-03-05T09:58:00.000+07:00

For all kamen rider fans, Here are Episode 6 which has been subbed!!

http://www.tvnihon.com/tracker/torrents-details.php?id=785

have a nice day

Kamen Rider Decade Eps 05 Subbed

2009-03-01T08:16:00.000+07:00

For all kamen rider fans, Here are Episode 5 which has been subbed!!

http://www.tvnihon.com/tracker/torrents-details.php?id=778

Kamen Rider Decade eps 04 Subbed

2009-02-19T12:35:00.001+07:00

For all kamen rider fans, Here are Episode 4 which has been subbed!!

http://tvnihon.com/tracker/download.php?id=766&name=[T-N]Kamen_Rider_Decade_04[B91A4800].avi.torrent

have a nice day

New Sql Server 2008 Query environment

2009-02-14T12:07:00.005+07:00

There's something new in sql server 2008 query environment.

See the pic below

it's just like you make a code at Visual studio environment. If you specify which table you will select than the columns name in the table will be display. No need to worry that you will write wrong columns name.

Only want to share this one for the moment. wait for my next explore of Sql server 2008.

Have a nice day.

Kamen Rider Decade eps - 03

2009-02-13T16:57:00.001+07:00

For all kamen rider fans, Here are Episode 3 which has been subbed!!

http://www.tvnihon.com/tracker/torrents-details.php?id=759

have a nice day

Kamen Rider Decade Eps 02 Subbed

2009-02-05T00:32:00.001+07:00

For all kamen rider fans, Here are Episode 2 which has been subbed!!

http://www.tvnihon.com/tracker/torrents-details.php?id=743

have a nice day

Kamen Rider Decade eps - 01

2009-01-31T09:23:00.002+07:00

For kamen rider fans, this is the link to download the subs for eps 1

Order of Zeronos - Kamen Rider Decade - 01
http://www.nyaatorrents.org/?page=download&tid=48378

#TV-Nihon - Kamen Rider Decade - 01
http://www.tvnihon.com/tracker/download.php?id=730&name=%5BT-N%5DKamen_Rider_Decade_01%5BB2A3D889%5D.avi.torrent

row_number function

2009-01-21T17:23:00.005+07:00

a new function in sql server 2005. It's function returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

syntax :

ROW_NUMBER ( ) OVER ( [<partition_by_clause>] <order_by_clause> )

example :

SELECT c.FirstName, c.LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0

(source : Sql server documentation )

great, doesn't it ?

have a try.

Have a nice day.

Pivot Table in Sql Server 2005 ( Part 2 )

2009-01-19T12:19:00.002+07:00

Here my logic to make the field more fleksible. So you don't have to fix the field. Of course this method used when you need to make your report fleksible and don't need to add the new field if there's a new data inserted into master table.

declare @field varchar(max)
declare @select varchar(max)
declare @employeeid int

set @field = ''

declare cr cursor for
select employeeid
from HumanResources.Employee

open cr

fetch next from cr into @employeeid

while @@fetch_status = 0
begin
if @field <> ''
begin
set @field = @field + ','
end

set @field = @field + '[' + convert(varchar(max),@employeeid ) + ']'

fetch next from cr into @employeeid
end

close cr
deallocate cr

set @select =
'SELECT VendorID, ' + @field + '
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @field + ' )
) AS pvt ORDER BY VendorID;'

exec (@select)

Have a try. Ask if you still confuse.

Have a nice day.

Pivot Table in Sql Server 2005

2009-01-06T17:20:00.003+07:00

New but not new since sql server 2008 have been launch. I will share a little bit about it.

Here's the sample query from Sql Server 2005 Documentation:

USE AdventureWorks
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID;

here is the result :

VendorID Emp1 Emp2 Emp3 Emp4 Emp5
1 4 3 5 4 4
2 4 1 5 5 5
3 4 3 5 4 4
4 4 2 5 5 4
5 5 1 5 5 5

if your database was upgraded from sql 2000 to sql 2005 , then you may set the compatiblel level to 90 to make the feature availeble.

sp_dbcmptlevel [ [ @dbname = ] name ] [ , [ @new_cmptlevel = ] version ]

example : sp_dbcmptlevel 'databasename', '90

remember : if you use pivot features you have to :
1. one column as the value to aggregate
2. one column as the column name . From the example , the column name would be employeeid.
Unfortunately, You have to define it.
I thought i can use

FOR EmployeeID IN
( select employeeid from HumanResources.Employee )

but it doesn't work, that will make my query more fleksible if it's work. I will find other way to make it. May be a little trik for it. I will share it on other post.
3. you are in sql 2005 - > there's must be obsolute.
4. the feature is on. use sp_dbcmplevel to change the level to 90.

How cool isn't it ? Have a try and have a nice day.

Kamen Rider Decade

2009-01-06T16:42:00.003+07:00

This year, the Kamen rider name Kamen Rider Decade. Decade, as its title suggests, is the tenth anniversary of the Heisei Rider Series, having begun with Kamen Rider Kuuga in 2000. It will be on air on January 25, 2009 in Japan.

You can find the group to discuss it at http://www.new.facebook.com/topic.php?topic=6418&post=22788&uid=30467778890#/group.php?gid=30467778890

Cann't wait to have it :D.

Mass Transfer Data

2008-12-31T07:53:00.003+07:00

Here my tips for newbie in sql server to transfer massive data from table A to table B.

example using adventuresworks from sql server 2005 database sample.

select * into table1
from HumanResources.Employee
where employeeid between 1 and 20

i create table1 with the same field as HumanResources.Employee. Above query just an example.

--- use it if you need to update some field of records in table1 that may need to be updated if
-- there's exists on destination table.
update HumanResources.Employee set vacationhours = 24
from HumanResources.Employee a
inner join tabel1 b on a.employeeid=b.employeeid

-- here the main massive insert. the order of the field may the same as the destination table.
-- in case there's different, but some of the them have the same field than you need to add
-- (nmfield1, nmfield2 , .. before select statement ) before the select statement.
-- Its better if you defined the field that you want to insert

insert into tabel1
select a.*
from HumanResources.Employee a
left join tabel1 b on a.employeeid=b.employeeid
where b.employeeid is null

Have a try and good luck .

Happy New Years 2009

Using Default Parameter in Store Procedure

2008-12-14T22:16:00.004+07:00

Sometime you need to create parameter with default value in your store procedure. Here is a simple example of how to make it:

create proc dbo.sp_getemployee
@employeeid char(10)= ''
as
select * from master_employee
where employeeid = case when @employeeid = '' then employeeid else @employeeid end

In here, i make a default value which is an empty string. I'm using it to manipulate the data that if the value is an empty string than return all employee data from employee table.

Now i can use it as below :

exec sp_getemployee

which is return all the record from master_employee

or

exec sp_getemployee @employeeid = '000000001'

which is return record with employeeid = '000000001'

How owsome isn't it ? but be carefule, it doesn't work if you create a report using Crystal report. Because, the crystal report will still ask to fill the value into the paramater.

Ok, now you know how to make default value into parameter in store procedure. Try it ok. Have a nice day

Store Procedure ( The Beginning )

2008-12-05T12:28:00.005+07:00

Stored Procedure ( next i call it SP ) is a saved collection of Transact-SQL statements that can take and return user-supplied parameters.

Syntax

CREATE PROC [ EDURE ] procedure _name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
(source SQL Help documentation)

That's a brief description and syntax about Stored Procedure.

Simple sample code :

/*
created : 10-12-2008 by Wihendro
Note : here you explain about the procedure function and purpose
*/
Create Proc dbo.spr_Test
as
set nocount on

select * from orders

That's all the brief SP best on my experience.
The /*...*/ ( a comment )filled in SP use to help us to monitor the changes made by the programmer. It may help us to track back the purpose of the SP. In application development using sql server as database, it may contain a lot of SP. After a decade of the development, if you need to change your code, you may encounter some difficulty about the purpose of the SP. So, in my opinion, you should use a comment above your SP.

"set nocount on" syntax use to set the message from sql server to off. every single statement either success or failed to execute, the sql server may send the message as part of the result execution. I used set nocount on to set it off. despite of receiving it, you may hide it. because you don't need it from your SP.

Ok, that's all for this time. For whom that doesn't know the SP , at least now know how to create it. and for others, may use my tips to add the comment and "set nocount on" on your SP. Wait for my next post more about Stored Procedure.

Have a Nice Day

Check .Net Framework 3.5 Compatibility Platform

2008-12-04T08:59:00.004+07:00

To install .net framework 3.5 at server you have to check your platform compatibility. Yesterday, i tried to install .Net framework 3.5 to my server. What i was not realize is that the server platform is Windows 2000 Server, unlucky me. Check this link to check the platform compatibility : http://msdn.microsoft.com/en-us/library/bb882520.aspx

Experience is a good teacher, that's right. So, before you choose what programming language and IDE to develop a Website project, do the following steps:
1. Choose programming language and IDE. in my case, i use VS 2008 for Asp.net with .net Framework 3.5
2. Check your server compatibility needed to deploy your Website.

Have a nice day.

Kamen Rider's Series

2008-12-02T15:31:00.007+07:00

it's along time ago, i falling love to Kamen rider series. Due to luck of information i just known some of them. Now, i took some time to search and listing it to have it :D.
This is a short list that i had know :
1. Kamen Rider Black
2. Kamen Rider Black Rx
3. Kamen Rider 555
4. Kamen Rider Kuuga
5. Kamen Rider Kabuto
6. Kamen Rider Hibiki
7. Kamen Rider Kiva
8. Kamen Rider Den-O
9. Kamen Rider Blade

I'm still searching the oldest one.
New update

Shōwa era

1971: Go Go Kamen Rider
1972: Kamen Rider Vs. Shocker
1972: Kamen Rider Vs. Hell Ambassador
1973: Kamen Rider V3
1973: Kamen Rider V3 vs. the Destron Monsters
1974: Kamen Rider X
1974: Kamen Rider X: The Five Riders Vs. King Dark
1975: Kamen Rider Amazon
1975: Kamen Rider Stronger
1980: Kamen Rider: Eight Riders vs. Galaxy King
1981: Kamen Rider Super-1
1988: Kamen Rider BLACK: Hurry to Evil Island
1988: Kamen Rider BLACK: Fear! Evil Monster Mansion
1989: Kamen Rider: Stay in the World - 3-D theme park special

Heisei era

1993: Kamen Rider ZO (1)
1994: Kamen Rider J (1)
1994: Kamen Rider World - 3-D theme park special
2001: Kamen Rider Agito: Project G4
2002: Kamen Rider Ryuki: Episode Final
2003: Kamen Rider 555: Paradise Lost
2004: Kamen Rider Blade: Missing Ace
2005: Kamen Rider Hibiki & The Seven Fighting Demons
2005: Kamen Rider The First
2006: Kamen Rider Kabuto: God Speed Love
2007: Kamen Rider Den-O: I'm Born!
2007: Kamen Rider The Next
2008: Kamen Rider Den-O & Kiva: Climax Deka
2008: Kamen Rider Kiva: King of the Castle in the Demon World
2008: Farewell, Kamen Rider Den-O: Final Countdow

Source (http://en.wikipedia.org/wiki/Kamen_Rider_Series#TV_series)

Return XML in Sql Server 2005

2008-10-17T10:03:00.005+07:00

one of the feature in sql server 2005 is returning the query in XML format.

Syntax :
[ FOR { BROWSE | } ]
::=
XML
{ {
RAW [ ( 'ElementName' ) ] | AUTO }
[
[ , { XMLDATA | XMLSCHEMA [ ( 'TargetNameSpaceURI' ) ] }
] [ , ELEMENTS [ XSINIL | ABSENT ] ]
| EXPLICIT
[ [ , XMLDATA ]
]
| PATH
[ ( 'ElementName' ) ]
[

[ , ELEMENTS [ XSINIL | ABSENT ] ]
]
}
Source: SQL Help Documentation

simple example :

select top 100 contactid, firstname, middlename, lastname
from Person.Contact
FOR XML AUTO, TYPE, XMLSCHEMA, ELEMENTS XSINIL

At .net application we only have to consume the xml result. How to do it, i will post it later because i still exploring it :D

Reset Visual SourceSafe 6.0 Admin

2008-10-13T09:52:00.006+07:00

Forgot your visual sourcesafe 6.0 admin ? i got the way after along time googling, now i will share it to you in case some day i forgot it again :D.

It's simple, just create a new sourcesafe database but don't forget the admin password. Than go to Data Folder ( your new database sourcesafe folder ) and find Um.dat. Copy the file to Data Folder ( the database sourcesafe folder which you want to open). Don't forget to backup the original first. After that try to log on using the new one.

Have a nice day.

Taxed Income Function: Case Indonesia

2008-10-10T08:54:00.017+07:00

Base on UU PPh pasal 21 in Indonesia, every working citizen have to pay income tax with progressive calculation tariff. The detail of this UU PPh will not discuss in here. What i will try to discuss is how to implemented it into sql server function dan store the tariff into tabel.

Even the progressive calculation tariff may stay for along time, it may possible that the rate may change or the placement. Real case is, the change of PPh 21 before 2008 change from 5 placement into 4 placement on 2009. You can imagine how you have to change all your calculation in your salary system if you use "fix code" method in your calculation system.

This is my idea to make the function and store the tariff into table. I Hope it will help you to efficient your job.

Here is some step you need to consider:
1. The function is for progressive tariff policy
2. There is a table to store the rate. it will be explain later.
3. The calculation is for Taxed Income, i will used PKP as the variable.

Firstly, we create the table:

CREATE TABLE [dbo].[Master_Tarif_Pajak](
[Tahun] [char](4) NOT NULL,
[Tingkat] [int] NOT NULL,
[Lower] [decimal](18, 0) NULL,
[Upper] [decimal](18, 0) NULL,
[Pajak] [decimal](5, 2) NULL,
CONSTRAINT [PK_Master_Tarif_Pajak] PRIMARY KEY CLUSTERED
(
[Tahun] ASC,
[Tingkat] ASC
)ON [PRIMARY]
) ON [PRIMARY]

The Requirement is :
Entry the record from the first placement to the fifth and the last one the upper we leave it Zero
Your data my looks like this :

Tahun Tingkat Lower Upper Pajak
2008 1 0 25000000 0.05
2008 2 25000000 50000000 0.10
2008 3 50000000 100000000 0.15
2008 4 100000000 200000000 0.25
2008 5 200000000 0 0.35
2009 1 0 50000000 0.05
2009 2 50000000 250000000 0.15
2009 3 250000000 500000000 0.25
2009 4 500000000 0 0.30

(9 row(s) affected)

Next step is the function :

/*
created : 29-08-2008 by wihendro

*/
create function dbo.UDF_Calculate_Tax
(
@pkp money , @tahun char(4)
)
returns money
as
begin
declare @tingkat int , @pajak money, @lower money, @upper money, @pajak1thn money
, @terhitung money, @denda money

set @tingkat = 1
set @pajak1thn = 0
set @terhitung = 0

while exists( select tingkat from master_tarif_pajak with (nolock )
where stsrc = 'A' and tahun = @tahun and tingkat = @tingkat
)
begin
select @lower = [lower], @upper = [upper] , @pajak = pajak
from master_tarif_pajak with (nolock )
where stsrc = 'A' and tahun = @tahun and tingkat = @tingkat

if @pkp > @upper
begin
if @upper = 0
begin
set @pajak1thn = @pajak1thn + ((@pkp-@terhitung)*@pajak)
break
end
else if @lower is not null
begin
if (@terhitung + ( @upper - @terhitung )) <= @pkp begin set @pajak1thn = @pajak1thn + ((@upper - @terhitung)*@pajak) set @terhitung = @terhitung + (@upper - @terhitung ) end else if (@terhitung + ( @upper - @terhitung )) > @pkp
begin
set @pajak1thn = @pajak1thn + ((@pkp - @terhitung)*@pajak)
set @terhitung = @terhitung + (@pkp - @terhitung)
break
end
else if @terhitung = @pkp
begin
break
end
else
begin
set @pajak1thn = @pajak1thn + ((@pkp - @terhitung)*@pajak)
set @terhitung = @terhitung + (@pkp - @terhitung)
break
end
end
else if (@terhitung + ( @upper - @terhitung )) > @pkp
begin
set @pajak1thn = @pajak1thn + ((@pkp - @terhitung)*@pajak)
set @terhitung = @terhitung + (@pkp - @terhitung)
break
end
else if @terhitung = @pkp
begin
break
end
end
else
begin
set @pajak1thn = @pajak1thn + ((@pkp-@terhitung) * @pajak )
break
end
set @tingkat = @tingkat + 1
set @lower = null
set @upper = null
end

return @pajak1thn
end

Example:

select Pajak = dbo.UDF_Calculate_Tax(200000000,'2008' )

Result :
Pajak
---------------------
36250000.00

(1 row(s) affected)

So, if next year, the role change into 4 placement ( see the sample data that i have created for you ), you only have to fill the tariff to the table and in your only have to send the value 2008 as the year. More effectively if you make an entry modul for the tariff table. So prepare your income tax calculation program to overcome the new UU Pph next year.

Note: You need to entry the new tariff every new begging year. Just don't forget to maintain the table ok.

Go to try it.
Good Luck.

User Defined Function

2008-10-07T09:25:00.009+07:00

In every sql statement you made, sometime there's sql statement that you use over and over. In a big project scale, there's a good idea to put it into Store Procedure or Function, depends on your needs. This time, i will discuss about Function,use to be konwn as a User-Defined Functions ( UDFs ).

User-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set. UDFs divide into 3 types, which are Scalar Functions, Inline table-valued and multiline table-valued

1. Scalar Functions
Syntax:

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ]
) RETURNS return_data_type [ WITH [ ,...n ] ] [ AS ]
BEGIN
function_body
RETURN scalar_expression
END [ ; ]
(Source : SQL Help Documentation)

sample :

create function dbo.udf_hitung_score
(
@nilai numeric
)returns char(11)
as
begin
declare @grade char(1)

set @grade = case when @nilai between 85 and 100 then 'A'
when @nilai between 70 and 84 then 'B'
when @nilai between 60 and 69 then 'C'
when @nilai between 50 and 59 then 'D'
else 'F' end
return(@grade)
end

Example :

select dbo.udf_hitung_score(82)

You can use it as follow :
example, you have Table A with attribut Mark contain Student Mark with Numeric data type, than your query may become like this :

select nama_mahasiswa, nama_matakuliah, kelas , grade = dbo.udf_hitung_score(nilai)
from A
order by kelas

2. Inline Table-Valued Functions
syntax:
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ]
) RETURNS TABLE [ WITH [ ,...n ] ] [ AS ]

RETURN [ ( ] select_stmt [ ) ] [ ; ]

(Source : SQL Help Documentation)

using Northwind as database sample :

CREATE FUNCTION dbo.udf_Master_customers
(
@customerid nchar(10)
) RETURNS TABLE
as
RETURN (select * from customers
where customerid = @customerid )

example :

select * from dbo.udf_Master_customers('Alfki')

you can join it to with others table.

3. Multistatement Table-valued Functions
Syntax :
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS @return_variable TABLE <> [ WITH [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END [ ; ]

(Source : SQL Help Documentation)

using Northwind as database sample :

CREATE FUNCTION dbo.udf_order
(
@customerid nchar(10)
) RETURNS @order TABLE (contactname varchar(60) ,orderid int, orderdate datetime
, requireddate datetime )
as
begin
declare @contactname varchar(60)

select @contactname = contactname
from customers where customerid = @customerid

insert @order
select @contactname , orderid, orderdate, requireddate
from orders
where customerid = @customerid

RETURN
end

example

select * from dbo.udf_order('alfki')

multistatement table-valued used if you need more logical programming to manipulate the data before displaying it.

Each type of UDFs has their own unique function depends on your needs in your project. But surely, it's very helpfull in your project later.

After this, i will try to collect some logic and make it as a function that may usefull for you.

Have a try and good luck.

Set nocount on

2008-09-27T11:56:00.004+07:00

Anyone know what is "set nocount on" ?
Its function to set the message from query result to off.

example:

select * from tabelA

Usually, come out the message besides the results, such as below:

(xx row(s) affected)

You use "Set nocount on" to make the message disappears. Depends on your needs. Sometimes, you use it to decrease the server peformance, that after displaying the result of the query, it may give you the message around the query results. For your concern, the error message still be display.

Base on my experience, I use "set nocount on" in my Store Procedure.The purpose is to lighten my Store Procedure performance that it decreased the server works to give the message to its caller.

Can you imagine when your Store Procedure need to insert a lots of records one by one. It may result one message every success inserted record. Plus, other message if you have select queyr in your Store Procedure.

Have a try and i hope it will useful for you.

Have a nice day

Left vs Right Join in SQL Language

2008-09-27T08:09:00.007+07:00

We know Left and Right Join in Sql Server. Do you know the differences between them ??
I will try to explain the difference between them and the tips from me in the usage.

Left Join, it use to join between 2 table which the left one will be display all records even there is no match record with the right one. Record with no match field partner will be display Null

The same for Right Join, we use it to join between 2 table and the right one will be display all records even there is no match record with the left one. Record with no match field partner will be display Null.

Sound difficult to understand with out the sample do you ?

Example:
Table A comes with Nim and Nama field ( have 3 records )
Tablel B comes with Nim and NamaMatakuliah ( have 5 records )

Select * from A left join B on a.nim=b.nim

The the results are minimum have 3 records plus the match record with table A

but when we change it into : Select * from A Right join B on a.nim=b.nim

then the results are minimum have 5 records plus the match record with table B.

Tips from me:
- Usually people much easier to compare between 2 table from left to right, So its better to use Left join rather than Right join.
- Before you create the query statement, make sure that the left table is the most records or it is the one that you want to keep it display the records even there is no match records in the join statement.

How is it ?There's a little bit understanding from my explain ?? Have a try, and ask if you still confuse about it.

Have a nice day.

A meaning of Failure

2008-09-26T12:16:00.008+07:00

Today, suddenly I would like to share about what failure means from my point of view.

In this life, we experience failures. However, do we know what failure means. A failure doesn't mean hope vanishes. Sometime in failure we found new hope. For example, a few centuries ago, before science about chemistry is found, a lot of scientist try to change the structure of various object into gold. With varieties of experiment, starting from burning, spells, meditation, etc. However, some of them really try scientifically. Although experiencing failures, they begin to understand, that on earth every object is created from various kind of compound that might be organic or inorganic. From which we more familiar as element chart we studied in chemistry.

That is only one example. Of course we need to see from the positive side, which is the firmness in achieving one's purpose. Is there anything we can study from that failure. How to fix is what we call continuous improvement.

So guys, what do you think about what failure means? Does it gives you spirit to be a better person? Please look into the mirror, me myself always look into the mirror from every failure I experienced.

Have a nice day.