How to Make Store Procedure for Save,Update,Delete, Cancel and Login Check

Store Procedure for Save,Update,Delete, Cancel and Login Check

This article explains why we should use store procedure and how to make store procedure

 # Stored procedure

-  Compiled object(bcoz object is compiled before therefore better performance)

-         To store in database (stored in db) less traffic for network

-         To execute in database (stored in db)

-         The stored procedure which is first time complied , it will come in SQl execution plan means sql cache memory (fastest memory)

-         Stored procedure are secure in comparison to inline query because sql injection doesnot apply on it.

-         Sql injection is a hacking technique which can be applied on inline query

# Types of Stored Procedure

1. User defined  Stored Procedure

2. System defined Stored Procedure

3. Extender defined Stored Procedure

# User defined  Stored Procedure – The stored procedure which are made by user.

Its types –

·        T-Sql Stored procedure

·        Clr Stored Procedure

* T-Sql Stored procedure – (Transect)group of sql statement which does not know array, object,connection etc

* Clr Stored procedure – sql+ clr classes

# System defined Stored Procedure – made by inbuilt ms stored procedure. Ex – sp_namedb, sp_helpconstraint, sp_addlogin

# Extended Stored procedure – To access the external component. Ex – in stored procedure we have to access calculator, notepad etc

# T- sql stored procedure

* Can stored procedure be nested

-Yes

* Upto what level

- Nesting can be done up to 31 level

# Stored procedure v/s Function

-         In backend both stored procedure and function returns the value

-         Stored procedures only returns the integer value

-         Function returns different type of data (varchar, string, integer)

-         We can use functions in queries in expressions

-         But we cannot use stored procedure in queries and expression

 
# How to create T- SQL Stored Procedure

Click on view> server explorer

Select your database > Tables>employee

Right click on Stored procedure > Add new stored procedure

# To save

CREATE PROCEDURE insemp       @eno int,       @en varchar(50),       @ed varchar(50),       @es int AS       insert employee values (@eno,@en,@ed,@es)

Click on update > update database

Insemp is stored procedure name

#Types of parameter

1. Input – to pass value

2. Output – to return value

3. InputOutput – parameter which can pass and return value

# By default size of varchar if not mentioned is 1

# For Update

CREATE PROCEDURE updemp       @eno int,       @en varchar(50),       @ed varchar(50),       @es int AS       update employee set ename=@en,eadd=@ed,esal=@es where empno=@eno

# For delete

CREATE PROCEDURE delemp       @eno int AS       delete from employee where empno=@eno

# For display

CREATE PROCEDURE dispemp       AS       select * from employee

 # For Find

CREATE PROCEDURE findemp       @eno int AS       select * from employee where empno=@eno

# Hacking

Make new table

Click on update> update database

Now click on server Explorer > stored procedure> add new stored procedure

CREATE PROCEDURE LoginCheck       @un varchar(50),       @up varchar(50) AS       declare @ap varchar(50)       select @ap=upass from tbuser where uname=@un       if @ap is null       return -1       else       if @ap=@up       return 1       else       return -2

       if @ap is null

      return -1            user name is wrong

      else

      if @ap=@up

      return 1             exact password

      else

      return -2            password wrong