SQL Server Hosting – How to Make Data Operation in SQL?

Peter Cole | May 19, 2016 | SQL 2016 Hosting | No Comments

In this post, let me show you how to make a data operation in SQL Server. You can learn with the following code:

USE [LeadManagement] GO  
/****** Object: StoredProcedure [dbo].[Category_InsertUpdate] Script Date: 05/16/2015 06:11:37 ******/  
SET  
ANSI_NULLS ON GO  
SET  
QUOTED_IDENTIFIER ON GO -- =============================================  
-- Author: <Chirag Patel>  
-- Create date: <18 Dec 2013>  
  -- Description:  <Insert/ Update Lead Category>  
  -- =============================================  
  ALTER PROCEDURE [dbo].[Category_InsertUpdate] @id int output,   
  @name nvarchar(50) AS BEGIN DECLARE @duplicate int IF(  
    @id = 0   
    OR @id IS NULL  
  ) BEGIN   
SELECT   
  @duplicate = COUNT(ID)   
FROM   
  CATEGORY   
WHERE   
  NAME = @name   
  AND IS_DELETED = 0 IF(@duplicate = 0) BEGIN INSERT INTO CATEGORY   
VALUES   
  (@name, 0)   
SELECT   
  @id = CAST(  
    SCOPE_IDENTITY() AS bigint  
  ) END ELSE   
SELECT   
  @id = CONVERT(bigint, '0') END ELSE BEGIN   
SELECT   
  @duplicate = COUNT(ID)   
FROM   
  CATEGORY   
WHERE   
  NAME = @name   
  AND ID <> @id   
  AND IS_DELETED = 0 IF(@duplicate = 0) BEGIN   
UPDATE   
  CATEGORY   
SET   
  NAME = @name   
WHERE   
  ID = @id   
SELECT   
  @id as ID END ELSE   
SELECT   
  @id = CONVERT(bigint, '0') END END USE [LeadManagement] GO   
  /****** Object: StoredProcedure [dbo].[Category_GetAll] Script Date: 05/16/2015 06:18:06 ******/  
SET   
  ANSI_NULLS ON GO   
SET   
  QUOTED_IDENTIFIER ON GO -- =============================================  
  -- Author:  <Chirag Patel>  
  -- Create date: <8 Nov 2013>  
  -- Description:  <Get all categories>  
  -- =============================================  
  ALTER PROCEDURE [dbo].[Category_GetAll] @id int AS BEGIN IF @id > 0   
SELECT   
  ID,   
  NAME   
FROM   
  CATEGORY   
WHERE   
  ID = @id   
  and IS_DELETED = 0   
ORDER BY   
  NAME ELSE   
SELECT   
  ID,   
  NAME   
FROM   
  CATEGORY   
WHERE   
  IS_DELETED = 0   
ORDER BY   
  NAME END USE [LeadManagement] GO   
  /****** Object: StoredProcedure [dbo].[Category_Delete] Script Date: 05/16/2015 06:18:22 ******/  
SET   
  ANSI_NULLS ON GO   
SET   
  QUOTED_IDENTIFIER ON GO -- =============================================  
  -- Author:  <Chirag Patel>  
  -- Create date: <18 Dec 2013>  
  -- Description:  <Delete Lead Category>  
  -- =============================================  
  ALTER PROCEDURE [dbo].[Category_Delete] @id nvarchar(200) AS BEGIN   
UPDATE   
  CATEGORY   
SET   
  IS_DELETED = 1   
WHERE   
  ID IN (  
    select   
      *   
    from   
      dbo.Split(@id, ',')  
  ) END

USE [LeadManagement] GO

/****** Object: StoredProcedure [dbo].[Category_InsertUpdate] Script Date: 05/16/2015 06:11:37 ******/

SET

ANSI_NULLS ON GO

SET

QUOTED_IDENTIFIER ON GO -- =============================================

-- Author: <Chirag Patel>

-- Create date: <18 Dec 2013>

-- Description: <Insert/ Update Lead Category>

-- =============================================

ALTER PROCEDURE [dbo].[Category_InsertUpdate] @id int output,

@name nvarchar(50) AS BEGIN DECLARE @duplicate int IF(

@id = 0

OR @id IS NULL

) BEGIN

SELECT

@duplicate = COUNT(ID)

FROM

CATEGORY

WHERE

NAME = @name

AND IS_DELETED = 0 IF(@duplicate = 0) BEGIN INSERT INTO CATEGORY

VALUES

(@name, 0)

SELECT

@id = CAST(

SCOPE_IDENTITY() AS bigint

) END ELSE

SELECT

@id = CONVERT(bigint, '0') END ELSE BEGIN

SELECT

@duplicate = COUNT(ID)

FROM

CATEGORY

WHERE

NAME = @name

AND ID <> @id

AND IS_DELETED = 0 IF(@duplicate = 0) BEGIN

UPDATE

CATEGORY

SET

NAME = @name

WHERE

ID = @id

SELECT

@id as ID END ELSE

SELECT

@id = CONVERT(bigint, '0') END END USE [LeadManagement] GO

/****** Object: StoredProcedure [dbo].[Category_GetAll] Script Date: 05/16/2015 06:18:06 ******/

SET

ANSI_NULLS ON GO

SET

QUOTED_IDENTIFIER ON GO -- =============================================

-- Author: <Chirag Patel>

-- Create date: <8 Nov 2013>

-- Description: <Get all categories>

-- =============================================

ALTER PROCEDURE [dbo].[Category_GetAll] @id int AS BEGIN IF @id > 0

SELECT

ID,

NAME

FROM

CATEGORY

WHERE

ID = @id

and IS_DELETED = 0

ORDER BY

NAME ELSE

SELECT

ID,

NAME

FROM

CATEGORY

WHERE

IS_DELETED = 0

ORDER BY

NAME END USE [LeadManagement] GO

/****** Object: StoredProcedure [dbo].[Category_Delete] Script Date: 05/16/2015 06:18:22 ******/

SET

ANSI_NULLS ON GO

SET

QUOTED_IDENTIFIER ON GO -- =============================================

-- Author: <Chirag Patel>

-- Create date: <18 Dec 2013>

-- Description: <Delete Lead Category>

-- =============================================

ALTER PROCEDURE [dbo].[Category_Delete] @id nvarchar(200) AS BEGIN

UPDATE

CATEGORY

SET

IS_DELETED = 1

WHERE

ID IN (

select

from

dbo.Split(@id, ',')

) END

Related Posts

About The Author

Pete