Performing Insert, Update, Delete Operations using Stored Procedure in SQL Server

In this code snippet you will learn to perform Insert, Update, Delete Operations using Stored Procedure in SQL Server.

Select ALL Users
------------------------
use dbtest
CREATE PROCEDURE [dbo].[usp_SelectAllUserDetails]
AS
BEGIN
select
*
From
tblUserDetails
END
GO
GetAllUserDetails
----------------------------------
CREATE PROCEDURE [dbo].[usp_GetAllUserDetails]
@PageIndex int = null,
@PageSize int = null,
@SortExp nvarchar(50),
@SortDir nvarchar(50) = 'desc'
AS
BEGIN
declare @tempStr as nvarchar(max)
if @SortExp is null or ( @SortExp is not null and len(@SortExp) =0 )
set @SortExp='createddatetime'
if @SortDir is null or ( @SortDir is not null and len(@SortDir) =0 )
set @SortDir='desc'
select * into #tblUserDetails
from
(
select * from tblUserDetails
)a
set @tempStr='select * from ( select ROW_NUMBER() OVER( ORDER BY ' + @SortExp + ' ' + @SortDir + ' ) AS rowNumber ,* from #tblUserDetails ) a where a.rowNumber between '
+ cast(@PageIndex as nvarchar(50)) + ' and ' + cast((@PageIndex + @PageSize - 1) as nvarchar(50))
if @PageSize is not null and @PageSize > 0
begin
exec(@tempStr)
End
Else
begin
select * from #tblUserDetails
End
SELECT count(*) 'RowCount' from #tblUserDetails
drop table #tblUserDetails
END
GO
GetUserDetails
----------------------------
CREATE PROCEDURE [dbo].[usp_GetUserDetails]
@ID int
as
Begin
if @ID is not null
Begin
select *
from tblUserDetails
where ID = @ID
End
else
Begin
select *
from tblUserDetails
where 1=0
End
End
GO
Delete UserDetails
----------------------------------
CREATE PROCEDURE [dbo].[usp_delete_UserDetails]
@ID int
as
Begin
delete
from tblUserDetails
where ID = @ID
Select @@Rowcount
END
GO
GetMaxIdForUserDetails
-----------------------------------------
CREATE PROCEDURE [dbo].[usp_GetMaxIdForUserDetails]
@tblUserDetails varchar(50)
As
Begin
If @tblUserDetails is not null
Begin
select isnull (ident_current(@tblUserDetails),1)
End
End
Go
Insert UserDetails
---------------------------------
Create procedure [dbo].[Usp_Insert_UserDetails]
@Fname nvarchar(50),
@Lname nvarchar(50),
@EmailAddress nvarchar(500),
@phone nvarchar(50),
@Photo nvarchar(100),
@CreatedBy uniqueidentifier,
@CreatedDatetime datetime,
@CreatedIp nvarchar(20),
@ModifiedBy uniqueidentifier,
@ModifiedDatetime datetime,
@ModifiedIp nvarchar(20)
as
Begin
insert into tblUserDetails
(Fname,
Lname,
EmailAddress,
phone,
Photo,
CreatedBy,
CreatedDatetime,
CreatedIp,
ModifiedBy,
ModifiedDatetime,
ModifiedIp)
values (@Fname,
@Lname,
@EmailAddress,
@phone,
@Photo,
@CreatedBy,
@CreatedDatetime,
@CreatedIp,
@ModifiedBy,
@ModifiedDatetime,
@ModifiedIp)
select ident_current('tblUserDetails')
End
Go
Update UserDetails
----------------------------------
Create procedure [dbo].[Usp_Update_UserDetails]
@ID int,
@Fname nvarchar(50),
@Lname nvarchar(50),
@EmailAddress nvarchar(500),
@phone nvarchar(50),
@Photo nvarchar(100),
@CreatedBy uniqueidentifier,
@CreatedDatetime datetime,
@CreatedIp nvarchar(20),
@ModifiedBy uniqueidentifier,
@ModifiedDatetime datetime,
@ModifiedIp nvarchar(20)
as
Begin
Update tblUserDetails set Fname = @Fname,
Lname = @Lname,
EmailAddress = @EmailAddress,
phone = @phone,
Photo = @Photo,
CreatedBy = @CreatedBy,
CreatedDatetime = @CreatedDatetime,
CreatedIp = @CreatedIp,
ModifiedBy = @ModifiedBy,
ModifiedDatetime = @ModifiedDatetime,
ModifiedIp = @ModifiedIp
where ID = @ID
select @ID
End
GO