Two Tips on Database

Stored Procedures

Description: Scripts that contains one or more lines of Transact-SQL (T-SQL) codes in order to run an execution to and from the database.


  • Faster execution and reducing network traffic; because the actual process running inside the server, not in the client-side.
  • Security mechanism; because the statements are stored inside the server, so there are no data transfers in form of query scripts on the network traffic.
/*Calculate product TotalCost*/
	@cOrderId char(6)
	SELECT TotalCost = ((siQty * mCost) - mDiscount)
	FROM OrderDetail
	WHERE cOrderId = @cOrderId


Description: Scripts that contains one or more lines of T-SQL codes in order to execute a group of statements to the database. This is usually used for Data Manipulation Language (DML) statements.


  • Can do checks to every related tables in a relationship.
  • Can do more complex ruleset or restriction to specific tables.
/*Automatically updates the numbers of quantity after sales*/
CREATE TRIGGER trgInventoryUsesiQty ON InventoryUse
DECLARE @inventoryid char(6),
	@inventoryuseqty smallint
SELECT @inventoryid = InventoryUse.cInventoryId,
@inventoryuseqty = InventoryUse.siQty FROM InventoryUse
INNER JOIN inserted ON InventoryUse.siInventoryUseId =
IF @inventoryuseqty >= 0
	UPDATE Inventories
	SET Inventories.siQoh = siQoh - @inventoryuseqty
	WHERE Inventories.cInventoryId = @inventoryid

Further Suggestions

Learn about what it called LINQ. LINQ is a set of T-SQL statements that can be directly integrate into a programming language. This is an applied technology introduce by Microsoft® in their current .NET environment.

Further Readings

There are a lot of other database techniques that can be used to simplify specific tasks according to the business process. This is also recommended for person who manage Active Directory Server, because actually the database works similar to Relational Database Management Systems (RDBMS). You can visit MSDN Library on SQL Server or any RDBMS websites to learn more about it.
Keywords: Cursors, Indexes, Views, Reporting Services.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: