Comman STORE PROCEDURE examples

 Here i setup some comman SQL STORE PROCEDURE Examples for insert update delete and get data from database

--Insert

Create PROCEDURE [dbo].[Insert]
(
@ID bigint output,
@colums nvarchar(150)=null
)
AS
Insert Into
table
(
[colums]
)
values(
@colums,

)
set @ID = Scope_identity()

--update
create PROCEDURE [dbo].[UpdateByPK]
(
@Id int,
@colums nvarchar(150)=null
)
AS
Update  table Set
[colums]=@colums

where
[Id]=@Id


Return


--get by pk
Create PROCEDURE [dbo].[GetByPK]
(
@Id int

)
AS
SELECT   
colums
FROM
Table 
WHERE
[Id]=@Id

RETURN


--get all

Create PROCEDURE [dbo].[Banner_GetAll]
(
@SortColumn varchar(50)='BannerId',
@SortOrder varchar(50)='DESC',
@CurrentPage int=1,
@NoOfRows int=0,
@CultureName char(12)=''
)
AS
DECLARE @Query nvarchar(MAX)

IF(@NoOfRows = 0)
BEGIN
    SET @Query = 'SELECT '
END
ELSE
BEGIN
    SET @Query = 'SELECT TOP (' + CONVERT(varchar,@NoOfRows) + ') '
END

SET @Query = @Query + '
 * FROM
(         
  SELECT ROW_NUMBER() over (ORDER BY ' + @SortColumn + ' ' + @SortOrder + ') as SrNo,           
  [Id],
[colums]
  FROM table

) AS Res'

IF(@NoOfRows <> 0)
BEGIN
    SET @Query = @Query + ' WHERE Res.SrNo > ' + CONVERT(varchar,(@NoOfRows * (@CurrentPage - 1)))
END

EXEC sp_executesql @Query

Return

--delete
Create PROCEDURE [dbo].[DeleteByPK]
(
@Id int
)
AS
Delete from
table
where
[Id]=@Id



---insert and update

create PROCEDURE [dbo].[Update_insert]
(
@Id bigint,
@Colums varchar(50)=null
)
AS
IF EXISTS(Select * from table Where [Id]=@Id
    Begin
        Update  table Set
        [Id]=@Id
        ,[colums]=@colums
       
        where
         [Id]=@Id
    End
Else
    Begin
        Insert Into
        table
        (
        [colums]
       
        )
        values(
        @Colums
        )
    End
Return