Generate System Column ID based on Format Required in SQL

If we want to make a generate id column in table which format is 'TR-091214-001' where,
- tr is define transaction
- 091213 is define yymmdd
- 001 is define increment number of transaction each day

we can make a simple logic and validation for that.

here the steps :

-- step 1 create table
create table testgenid
(
tr_id varchar(30) not null,
primary key(tr_id)
)

-- step 2 generate id
declare @gen_id varchar(30)
set @gen_id = (
select 'TR-'+ right(convert(varchar,getdate(),112),6) + '-' + right('00' + cast((cast(right(isnull(max(tr_id),'000'),3) as int) + 1) as varchar),3)
from testgenid
where tr_id like '%' + right(convert(varchar,getdate(),112),6) + '%')

print @gen_id

insert into testgenid values(@gen_id)

-- step 3 see the result
select * From testgenid

the result is : TR-091214-001

happy coding ^^

Data Dictionary Using Query SQL

When we want to summarize all the tables on database from SQL we need the query to make it simple and fast... here the query :

select distinct b.name, a.name, c.name, a.max_length,c.precision, c.scale
from sys.columns a left join sys.objects b
on a.object_id = b.object_id
left join sys.types c
on c.system_type_id = a.system_type_id
and c.user_type_id = a.user_type_id

Having A Nice Try… ^^

Good Luck

Row Number Function in SQL 2005

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 ( [] )

Example :

create table Testing ( Kd varchar(10) not null, Name varchar(10) )

insert into Testing values('K01','Siji')
insert into Testing values('K02','Loro')
insert into Testing values('K03','Telu')
insert into Testing values('K04','Opat')

select ROW_NUMBER() OVER(ORDER BY Kd) AS 'No.' , Kd , Name from Testing

Output :

Easy Right!!!

Happy Code \^_^/

Find Text In SQL Server

Sometimes we want to check where the certain text could appear on the store procedure or trigger we made... Here is the query provide to maximize the searching :


declare @text varchar(max)
set @text = 'Wihemdra'

select name from sys.objects
where object_id in
(
select id from syscomments
where text like '%'+@text+'%'
)


Regards,
Wihemdra

Example Duwamish Architecture with ASP.NET C#

Above is the screen capture for duwamish layer template.

The Duwamish 7.0 architecture is divided into four logical layers:

Web Layer
The Web layer provides access for clients to the application. This layer is implemented as the Web project in the Duwamish.sln solution file. The Web layer consists of ASP.NET Web Forms and code-behind files. The Web Forms simply provide the user experience in HTML, while the code-behind files implement event handling for the various controls.

Business Facade Layer
The Business Facade layer provides interfaces to the Web layer to handle accounts, category browsing, and book purchasing. This layer is implemented as the BusinessFacade project in the Duwamish.sln solution file. The Business Facade layer serves as an isolation layer, segregating the user interface from the implementation of the various business functions. Apart from low-level system and support functions, all calls to database servers are made through this assembly.

Business Rules Layer
The Business Rules layer, which is implemented as the BusinessRules project in the Duwamish.sln solution file, contains the implementation of the various business rules and logic. Business rules do tasks such as the validation of customer accounts and book orders.

Data Access Layer
The Data Access layer provides data services to the Business Rules layer. This layer is implemented as the DataAccess project in the Duwamish.sln solution file.

Download Template

Reference :

MSDN

Design Dynamic Structure Table

Design table for meet the requirement is not so simple. Sometimes rule and process of business change rapidly. We must predict the future before we start to create the table. The table structure we made could impact anywhere which related it. As you could see the logic for application , reports, calculation, batch, etc depend on the design of the table. Sometimes we think the table is unpowerfull or too much field unrequired...

One example to handle this requirement for current and future development :

The requirement is : every product have own rate accumulated per year, and not every year is at the same rate but we assumed that the maximum rate until 100 years or less. So the simulation is like this :

--bad design table
--take too much columns
create table mst_rate_product
(
code_product nvarchar(20) not null,
rate_year01 decimal(10,4),
rate_year02 decimal(10,4),
rate_year03 decimal(10,4),
rate_year04 decimal(10,4),
rate_year05 decimal(10,4),
rate_year06 decimal(10,4),
rate_year07 decimal(10,4),
rate_year08 decimal(10,4),
rate_year09 decimal(10,4),
rate_year10 decimal(10,4),
--...
rate_year100 decimal(10,4)
primary key (code_product)
)

--recommended design table structured
--powerfull on data requirements and saving data
create table mst_rate_product_recommended
(
code_product nvarchar(20) not null,
year int,
rate decimal(10,4),
primary key (code_product)
)

We don't have to affraid to make a complicated report by logic because sql server 2005 have pivot table to make a report based on column(if needed) not by rows.

Regards,
Wihemdra

Using Begin Tran, Rollback Tran and Commit Tran on SQL Server

Sometimes, we're using too much windows document in sql server.
To prevent the mass Execute query on one window we didn't want, better we use query "begin tran" in every windows document.
So, if we're executing the windows document by accident, we could rollback all the transaction we made ^^".

-----------------------------Assumed this is the windows document used
Begin Tran

select * from nametable

insert into nametable values('string value')
insert into nametable values('string value')
insert into nametable values('string value')
insert into nametable values('string value')
insert into nametable values('string value')
insert into nametable values('string value')

update nametable
set namecolumn = 'asdf'

/*
When we Execute execute for the second time, but we want to cancel it. Just use rollback tran. But otherwise use commit tran
*/

Regards,
Wihemdra