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

1.9k
0
0

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