June 5, 2012

Stored procedure to Insert, Update and delete records

You can write Stored procedure following  to Insert, Update and delete records from tables in sql.

1. Stored procedure to Insert records in table.



 set ANSI_NULLS ON  
 set QUOTED_IDENTIFIER ON  
 GO  
 Create PROCEDURE [dbo].[SP_ITEM_Insert]  
 @CD char(7),  
 @ITEM_DESC CHAR(60)  
 AS  
 INSERT [dbo].[ITEM]  
 (  
 [CD],  
 [ITEM_DESC]  
 )  
 VALUES  
 (  
 @CD,  
 @ITEM_DESC  
 )  
2. Example of Stored procedure to Update data in to table .

 set ANSI_NULLS ON  
 set QUOTED_IDENTIFIER ON  
 GO  
 Create PROCEDURE [dbo].[SP_ITEM_Update]  
 @CD char(7),  
 @ITEM_DESC char(60)  
 AS  
 UPDATE [dbo].[ITEM]  
 SET   
 [ITEM_DESC] = @ITEM_DESC  
 WHERE  
 [CD] = @ CD  

3. Example of Stored procedure to Update data in to table



 set ANSI_NULLS ON  
 set QUOTED_IDENTIFIER ON  
 GO  
 Create PROCEDURE [dbo].[SP_ITEM_ Delete]  
 @ CD char(7)  
 AS  
 DELETE FROM [dbo].[ITEM]  
 WHERE  
 [CD] = @CD  
You can set more conditions and columns as per requirement .