Sample Stored Procedure_2

Sample Stored Procedure_2

*Create select procedure

Create Proc SelectEmp_SP

(

@UserId         nvarchar(50),

@UserName  nvarchar(50),

@UserState    nvarchar(50),

@UserCity      nvarchar(50),

@Gender        nvarchar(50),

@Course         nvarchar(50),

@UserAddress nvarchar(50),         

@PhoneNo     numeric(10, 0)          

)

as       

insert into Employee(UserId,UserName,UserState,UserCity,Gender,Course,UserAddress,PhoneNo) values(@UserId,@UserName,@UserState,@UserCity,@Gender,@Course,@UserAddress,@PhoneNo)

return

 *Create update procedure

Create Proc UpdateEmp_SP

(

@UserId        nvarchar(50),          

@UserName nvarchar(50),          

@UserState   nvarchar(50),          

@UserCity     nvarchar(50),          

@Gender       nvarchar(50),

@Course        nvarchar(50),          

@UserAddress nvarchar(50),       

@PhoneNo   numeric(10, 0)        

)

as       

Update Employee set UserName=@UserName,UserState=@UserState,UserCity=@UserCity,Gender=@Gender,Course=@Course,UserAddress=@UserAddress,PhoneNo=@PhoneNo where UserId=@UserId

return

 *Create delete procedure

Create Proc DeleteEmp_SP

(

@UserId        nvarchar(50)

)

as       

Delete from Employee where UserId=@UserId

return

 *Create insert procedure

Create proc UDP_INSStudent

@UserName nvarchar(50),

@FatherName nvarchar(50),       

@UserState   nvarchar(50),          

@City nvarchar(50),

@Gender       nvarchar(50),          

@PhoneNo   numeric(10, 0)

as

Insert into Student(UserName,FatherName,UserState,City,Gender,PhoneNo) Values(@UserName,@FatherName,@UserState,@City,@Gender,@PhoneNo)

go

      

 *Alter insert procedure

Alter proc UDP_INSStudent

@UserId nvarchar(50),

@UserName nvarchar(50),

@FatherName         nvarchar(50),          

@UserState   nvarchar(50),          

@City nvarchar(50),

@Gender       nvarchar(50),          

@PhoneNo   numeric(10, 0)

as

Insert into Student(UserId,UserName,FatherName,UserState,City,Gender,PhoneNo) Values(@UserId,@UserName,@FatherName,@UserState,@City,@Gender,@PhoneNo)         

go

 *Alter Update procedure

Alter proc UDP_UPDStudent

@UserId nvarchar(50),

@UserName nvarchar(50),

@FatherName         nvarchar(50),          

@UserState   nvarchar(50),          

@City nvarchar(50),

@Gender       nvarchar(50),          

@PhoneNo   numeric(10, 0)

as

update Student set UserName=@UserName,FatherName=@FatherName,UserState=@UserState,City=@City,Gender=@Gender,PhoneNo=@PhoneNo where UserId=@UserId

go

                   

 *Alter Delete procedure

Alter proc UDP_DELStudent

@UserId nvarchar(50),

@UserName nvarchar(50),

@FatherName         nvarchar(50),          

@UserState   nvarchar(50),          

@City nvarchar(50),

@Gender       nvarchar(50),          

@PhoneNo   numeric(10, 0)

as

begin

delete from Student where UserId=@UserId

end

    

 *Alter Delete procedure

Alter proc UDP_DELStudent

@UserId nvarchar(50)

As

begin

delete from Student where UserId=@UserId

End

    

 *Alter insert procedure

Alter proc UDP_INSStudent 

@UserId nvarchar(50), 

@UserName nvarchar(50), 

@FatherName nvarchar(50),  

@UserState nvarchar(50),  

@City nvarchar(50), 

@Gender nvarchar(50),  

@PhoneNo numeric(10, 0) 

as

begin 

Insert into Student(UserId,UserName,FatherName,UserState,City,Gender,PhoneNo) Values(@UserId,@UserName,@FatherName,@UserState,@City,@Gender,@PhoneNo)

end

  

*Alter Update procedure

Alter proc UDP_UPDStudent 

@UserId nvarchar(50), 

@UserName nvarchar(50), 

@FatherName nvarchar(50),  

@UserState nvarchar(50),  

@City nvarchar(50), 

@Gender nvarchar(50),  

@PhoneNo numeric(10, 0) 

as

begin

update Student set UserName=@UserName,FatherName=@FatherName,UserState=@UserState,City=@City,Gender=@Gender,PhoneNo=@PhoneNo where UserId=@UserId

END

 *Select with Count aggregate function

select Department.DeptName, COUNT(*) as Total from Employee

join Department ON Department.Id=Employee.DepartmentId group by DeptName

Find Salary up to Maximum Number

1.select MAX(Salary) from UserLogin where Salary < (select MAX(Salary) from UserLogin)

2.select Top 1 Salary from(select DISTINCT Top 3 Salary from UserLogin orderby Salary Desc) Result orderby Salary ASC

3. with Result As

(

select Salary, DENSE_RANK() over (orderby Salary DESC) as DENSRANK from UserLogin

)

select Top 1 Salary from Result where Result.DENSRANK=4

Default Constraints

ALTER TABLE tblPerson ADD CONSTRAINT DF_tblPerson_GenderID DEFAULT 3 FOR GenderID

Set Identity_Insert tblPerson on/Set Identity_Insert tblPerson off

https://www.youtube.com/channel/UCKLRUr6U5OFeu7FLOpQ-FSw/videos