Stored procedures in sql server 2008


Definition: 
----------------
               Stored procedures are precompiled database queries that improve the security,
efficiency and usability of database client/server applications. Developers specify a
stored procedure in terms of input and output variables. They then compile the code on
the database platform and make it available to aplication developers for use in other
environments, such as web applications. All of the major database platforms, including
Oracle, SQL Server and MySQL support stored procedures. The major benefits of this
technology are the substantial performance gains from precompiled execution, the
reduction of client/server traffic, development efficiency gains from code reuse and
abstraction and the security controls inherent in granting users permissions on specific
stored procedures instead of the underlying database tables.

Syntax:
___________
CREATE PROCEDURE PROCEDURE_NAME(PARAMETERS/ARGUMENTS PASSING)
AS
BEGIN

     //INSERT OR UPDATE OR DELETE STATEMENTS//
END

EXAMPLE FOR INSERT AND UPDATE PROCEDURES:
----------------------------------------------------------------------
CREATE  procedure [dbo].[ActivityInsert]
(
@Type varchar(50),
@EmpId varchar(50),
@Category varchar(10),
@Outline Varchar(200),
@Narration Varchar(MAX),
@Remarks Varchar(200),
@Checked Varchar(50),
@Verified Varchar(50),
@Date date

)
as
begin
if(@Type='Insert')
begin
insert into ABSL_ActivityReport(EmpId,Category,Outline,Narration,Remarks,Checked,Verified,Date,CreatedDate)
values(@EmpId,
@Category,
@Outline,
@Narration,
@Remarks,
@Checked,
@Verified,
@Date,
GETDATE())
end
else
begin
update ABSL_ActivityReport set  Category=@Category,Outline=@Outline,Narration=@Narration,Remarks=@Remarks,Checked=@Checked,
Verified=@Verified,Date=@Date,ModifiedDate=GETDATE() where EmpId=@EmpId
end

end




EXAMPLE FOR DELETE PROCEDURE:
_____________________________________

Create  procedure [dbo].[CarParking_Delete]
(
  @Id int
)
as
begin
Delete from CarParking_Create where Id=@Id
end




How to Execute Procedures :
-----------------------------------------

EXEC PROCEDURE_NAME VALUES

Example:

     EXEC CarParking_Delete 1
   
          Here CarParking_Delete is Name of the Procedure and 1 is the id which is
Deleted By us.Means which row we want to delete from the front end.

4 comments:

  1. can u plz help ..how to call user defined functions in sql server 2005

    suppose i have a function which takes 2 input arguments and 1 output variable.
    thanks

    email id: bhatimohit@gmail.com

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. how to conver stored procedures to dreamweaver cs6

    ReplyDelete