Stored Procedure vs Triggers

Last time, I wrote an article and focussed on Using SELECT, Views and Stored Procedures in SQL Server. I was quite glad to differentiate the three and its Pros & Cons. I was not even given much importance of Trigger not because of NO TIME. To be honest, I was just a beginner in use of Triggers. In this article I decided to write about Triggers and its difference with Stored Procedure. We had a team meeting to incorporate the audit information in our organization.

After a few months, we had a debate to incorporate the audit information either in Stored Procedure or Trigger. In the meeting, the set of team member has suggested having audit statements in Triggers and another set of team member has recommended having audit statements in Stored Procedures. I was totally confused to take a decision. Then, I told my team members to stop the debate. I requested my team members to involve in R&D for few days before come to the conclusion and judge the Pros & Cons of the two. This is how the article has been born to have audit information in our project. I devoted my influence to write an article on Stored ProcedureVsTrigger and to differentiate the two and its Pros & Cons.

So, how do I start! I decided to use some information from my previous article http://www.sql-server-performance.com/gv_sp_views_selects.asp. I have introduced the characters that would be involving in the article before the initiative. These characters would play important role in the article.

When To Use the Order By Clause

When I was using SQL Server, I was not using the "order by" clause properly. I always say myself that when I want the data in a particular order, I should use order by clause in select statement otherwise leave to the SQL Server. I presumed MS-SQL Server will return the data in its own order. I was not even thinking that am I using and doing correctly. In my initially days in my career, I was happy to retrieve the data and not knowing how it comes. It is the nature for the software engineers at the premature stage or using new command or tool or technology. They will not knowing properly or doing enough research of the each command before commence using the command. I would say it could be because of competition in the IT field and immature in IT field.

Where do we get the word "Order?" I have heard and seen in so many places the use of "Order" word. I have come across the listed places the "Order" word used regularly.


When an applicant applies for a student visa, visa officer will politely inform to the application order the applications and come back.
When a procurer gives a draft to the registrar officer, officer will politely return back the draft and request to order the draft.
Firm will display the designation in a proper order.
Construct a house; we will clean and even the land before establishing the Pillar.



The ultimate aim is "First IN has to first come OUT"

I have decided and kept in my mind that somewhere down the line in my IT career, I should know when I should use "order by" clause in the select statement. I know it will be on one FINE DAY". I will spend some time on "order by" command. That one fine day is TODAY to explore myself and find the ways to use the "order by" clause. This could be useful for a new developer and dba to SQL Server to avoid confusion on using "order by clause" and knowing when to use "order by" clause command.

To come along with me in this article, I have paste the script and result of the output. Example of the script can be used in a sql server to understand completely after reading the article.

DisplayColumnNames

This sp will return the column names for a table(s) and form a "Select Statement" for a table(s). We can pass a table name as a parameter to display a "Select Statement" for that table.

if exists (select * from sysobjects where id = object_id(N'[dbo].[spDisplayColumnName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spDisplayColumnName]
GO
Create Proc spDisplayColumnName (@TableName Varchar(100)='') As
--Exec spDisplayColumnName 'Account'
--Or
--Exec spDisplayColumnName
Declare @ColumnName Varchar(8000), @TableID Int

Set Nocount On

If Len(@TableName) = 0
Begin
DECLARE DisplayColumnName_Cursor CURSOR FOR
Select SO.ID From SysObjects SO Where xType = 'U' Order By SO.Name
End
Else
Begin
If Not Exists(Select 1 From SysObjects Where ID = Object_ID(@TableName) And xType = 'U')
Begin
Print 'Passed parameter [' + @TableName + '] is not an User table'
Return 0
End
Else
Begin
DECLARE DisplayColumnName_Cursor CURSOR FOR
Select SO.ID From SysObjects SO Where ID = Object_ID(@TableName)
And xType = 'U' Order By SO.Name
End
End

OPEN DisplayColumnName_Cursor

FETCH NEXT FROM DisplayColumnName_Cursor
INTO @TableID

WHILE @@FETCH_STATUS = 0
Begin
Set @ColumnName = ''
Select @ColumnName = @ColumnName + ', ' +Name
From SysColumns Where ID = @TableID
Order By Name
Print Object_Name(@TableID)
Select 'Select ' + Right(@ColumnName,Len(@ColumnName)-1) + ' From ' + Object_Name(@TableID)

FETCH NEXT FROM DisplayColumnName_Cursor
INTO @TableID
End
CLOSE DisplayColumnName_Cursor
DEALLOCATE DisplayColumnName_Cursor

Set Nocount Off
Return 0