SQL Server Hosting – How to Make Data Operation in SQL?
In this post, let me show you how to make a data operation in SQL Server. You can learn with the following code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 |
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
