Tuesday, December 22, 2009

Sql Server- Difference between Stored Procedure and Function


In Sql Server, both functions and stored procedures can be defined as the logically grouping of Sql statements formed to complete a task, but still there are many differences between Stored procedure and Functions.

These differences can be summarized as follows:-

1) A stored procedure can return a value or it may not return any value but in case of function, a function has to return a value.

2) Stored procedure in Sql Server can not we executed within the DML statement.It has to be executed with the help of EXEC or EXECUTE keyword but a function can be executed within the DML statement. 

3) Functions can be called from within the Stored Procedure but a stored procedure can not be called from within a function.

4) A stored procedure can return multiple parameters but a function can return only one value.

5) We can use join in the outcome from the functions but we can't use joins in the outcome from stored procedures.

6) Transaction management is not possible in functions but it is possible in Stored procedures.

7)Print function can not be called within the function but it can be called within the stored procedure.

Thursday, December 17, 2009

Difference between Delete and Truncate Command

Although the Delete and Truncate Commands logically does the same work of deleting the rows from the table but still there are many differences in their working. These differences are given below:-

1) Delete command maintained the logs files of each deleted row but Truncate command do not maintain the logs files for each deleted row but maintains the record for deallocation of the datapages in the log files.The deallocation of the datafiles means that the data rows still exists in the data pages but the extends have marked as empty for reuse.

2) Truncate command is much faster than delete command.

3) You can use Where clause in case of Delete command to delete a particular row but in case of Truncate command you have to delete the data from all the row since Where clause is not work with Truncate command.

4) Triggers is fired in case of Delete command only and they are not fired when Truncate command is used.

5) Truncate command resets the Identity property to its initial value whereas Delete command do not resets the Identity property of the column.

6) Delete is a DML command and Truncate is a DDL command.

Sunday, December 6, 2009

Sql Server - How to write a Stored procedure in Sql server

Stored Procedure:- Stored Procedure In Sql server can be defined as the set of logically group of sql statement which are grouped to perform a specific task. There are many benefits of  using a stored procedure. The main benefit of  using a stored procedure is that it increases the performance of the database.The other benefits of using the Stored Procedure is given below:-

Benefits of using the Stored Procedure:-

1) One of the main benefit of using the Stored procedure is that it  reduces the amount of information sent to the database server. It can become more important benefit when the bandwidth of the network is less. Since if we send the sql query (statement)which is executing in a loop to the server through network and the network get disconnected then the execution of the sql statement don't returns the expected results, if the sql query is not  used between Transaction statement and rollback statement is not used.

2) Compilation step is required only once when the stored procedure is created. Then after it  does not required recompilation before executing unless it is modified and re utilizes the same execution plan whereas the sql statements needs to be compiled every time whenever it is sent for execution even if we send the same sql statement every time.

3) It helps in re usability of the sql code because it can be used by multiple users and by multiple client since we needs to just call the stored procedure instead of writing the same sql statement every time. It helps in reduces the development time.

4) Stored procedure is helpful in enhancing the security since we can grant permission to the user for executing the Stored procedure instead of giving the permission on the tables used in the Stored procedure. 

5) Sometime it is useful to use the database for storing the business logic in the form of stored procedure since it make it secure and if any change is needed in the business logic then we may only need to make changes in the stored procedure and not in the files contained on the web server.
How to write a Stored Procedure in Sql server:-
 
Suppose there is a table called tbl_Students whose structure is given below:-

CREATE TABLE  tbl_Students
(
    [Studentid] [int] IDENTITY(1,1) NOT NULL,
    [Firstname] [nvarchar](200) NOT  NULL,
    [Lastname] [nvarchar](200)  NULL,
    [Email] [nvarchar](100)  NULL
)

Support we insert the following data into the above table:-

Insert into tbl_Students (Firstname, lastname, Email)
 Values('Vivek', 'Johari', 'vivek@abc.com')

Insert into tbl_Students (Firstname, lastname, Email)
 Values('Pankaj', 'Kumar', 'pankaj@abc.com')

Insert into tbl_Students (Firstname, lastname, Email)
 Values('Amit', 'Singh', 'amit@abc.com')

Insert into tbl_Students (Firstname, lastname, Email)
 Values('Manish', 'Kumar', 'manish@abc.comm')

Insert into tbl_Students (Firstname, lastname, Email)
 Values('Abhishek', 'Singh', 'abhishek@abc.com')

Now, while writing a Stored Procedure,  the first step will be to write the Create Procedure statement as the first statement,

Create Procedure Procedure-name 
(
Input parameters ,
Output Parameters (If required)
)

As
Begin

     Sql statement used in the stored procedure
End

Now,  suppose we need to create a Stored Procedure which will returns a student name whose studentid is given as the input parameter to the stored procedure. Then the Stored Procedure will be:-

/*  Getstudentname is the name of the stored procedure*/
Create  PROCEDURE Getstudentname
(
@studentid INT                   --Input parameter ,  Studentid of the student
)
AS
BEGIN
SELECT Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid
END

We can also collect the student name in the output parameter of the Stored Procedure.For Example:-

 /* 
GetstudentnameInOutputVariable is the name of the stored procedure which uses output variable @Studentname to collect the student name returns by the stored procedure
*/

Create  PROCEDURE GetstudentnameInOutputVariable
(

@studentid INT,                                          --Input parameter ,  Studentid of the student
@studentname VARCHAR(200)  OUT        -- Out parameter declared with the help of OUT keyword
)
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid
END

Note:-/* */ is used to write comments in one or multiple lines
           --     is used to write comment in a single line

How to Alter a Stored Procedure in a Sql server:-

In Sql Server, a stored procedure can be modified with the help of the Alter keyword.Now if, we want to get student email address through the same procedure GetstudentnameInOutputVariable . So we need to modified it by adding one more output parameter " @StudentEmail " which is shown below:-

/* 
Stored Procedure GetstudentnameInOutputVariable is modified to collect the email address of the student with the help of the Alert Keyword
*/
Alter  PROCEDURE GetstudentnameInOutputVariable
(

@studentid INT,                   --Input parameter ,  Studentid of the student
@studentname VARCHAR (200) OUT,    -- Output parameter to collect the student name
@StudentEmail VARCHAR (200)OUT     -- Output Parameter to collect the student email
)
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname, @StudentEmail=email FROM tbl_Students WHERE studentid=@studentid
END

Note:- It is not necessary that a stored procedure will have to written. It can be the case when a stored procedure doesn't written any thing. For Example, a stored procedure can be used to Insert, delete or update a sql statement. For Example the below stored procedure is used tp insert value into the table tbl_students.

/*
This Stored procedure is used to Insert value into the table tbl_students.
*/
Create Procedure InsertStudentrecord
(
 @StudentFirstName Varchar(200),
 @StudentLastName  Varchar(200),
 @StudentEmail     Varchar(50)
)
As
 Begin
   Insert into tbl_Students (Firstname, lastname, Email)
   Values(@StudentFirstName, @StudentLastName,@StudentEmail)
 End
Execution of the Stored Procedure in Sql server:-

   Execution of the Stored Procedure which don't have a Output Parameter:-
   A stored procedure is used in the Sql server with the help of the "Execute" or "Exec" Keyword. For Example , If we want to execute the stored procedure "Getstudentname", then we will use the following statement:-

Execute Getstudentname 1
Exec Getstudentname 1
Execution of the Stored Procedure using the Output Parameter:-
     If we want to execute the Stored procedure "GetstudentnameInOutputVariable " , then we first need to declare the variable to collect the output values. For example

Declare @Studentname as nvarchar(200)   -- Declaring the variable to collect the Studentname
Declare @Studentemail as nvarchar(50)     -- Declaring the variable to collect the Studentemail
Execute GetstudentnameInOutputVariable 1 , @Studentname output, @Studentemail output
select @Studentname,@Studentemail      -- "Select" Statement is used to show the output from Procedure
Summary:-
In the end we can say that a Stored procedure not only enhancing the possibility of reusing the code and execution plan,  but it also increases the performance of the database by reducing the traffic of the network by reducing the amount of information send over the network.
DMCA.com