I want to create a single sp for insert update and delete

Posted by Suneel161 under Regular Expressions on 3/10/2010 | Views : 2660 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

hiii
I want to create a single sp for insert update and delete.And how to call in programatically..

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Ecethatha on: 3/10/2010 [Member] Starter

0	As per my knowledge we cann't create a single SP for multiple Sql statements. We have to create separate SP for each sql statement. I don't think so.....4 multiple...k let see can we create or not???? i m also waiting 4 answer Suneel161, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Abhi2434 on: 3/10/2010 [Member] [Microsoft_MVP] [MVP] Silver

0	I think basically we do create CRUD stored procedures to handle all the operations. Say for instance your CRUD Stored proc looks like : CREATE PROCEDURE [dbo].[UP_CRUD_MYTABLE] ( /@P_OPMODE IS TO DEFINE DML INTERACTION MODE INSERT=1, UPDATE=2, DELETE=3, RETRIVE=4/ @P_OPMODE INT = 4, @P_ID NVARCHAR(50) = NULL, @P_COLUM1 NVARCHAR(50) = NULL, @P_COLUM2 NVARCHAR(50) = NULL, @P_NAME NVARCHAR(50) = NULL, @P_EXECSTATUS BIT = 0 OUTPUT, @P_ERRORNO DECIMAL = NULL OUTPUT, @P_ERRORMSG NVARCHAR(2000) = NULL OUTPUT ) AS BEGIN BEGIN TRY IF @P_OPMODE = 1 BEGIN IF @P_ID <> NULL BEGIN INSERT INTO MYTABLE ...... END END ELSE IF @P_OPMODE = 2 BEGIN UPDATE MYTABLE ... END ELSE IF @P_OPMODE = 3 BEGIN DELETE FROM MYTABLE WHERE ..... END ELSE IF @P_OPMODE=4 BEGIN SELECT * FROM MYTABLE WHERE .... END SET @P_EXECSTATUS =1 END TRY BEGIN CATCH EXECUTE UP_LOG_PROCEDUREERROR /* TO LOG PROCEDURE ERRORS / SET @P_ERRORMSG = ERROR_MESSAGE() SET @P_ERRORNO = ERROR_NUMBER() END CATCH END Something similar to this. Call this with P_OPMODE appropriately..... Also make sure you write your DML statements according to what you need www.abhisheksur.com Suneel161, if this helps please login to Mark As Answer*. \| Alert Moderator

I think basically we do create CRUD stored procedures to handle all the operations.

Say for instance your CRUD Stored proc looks like :


CREATE PROCEDURE [dbo].[UP_CRUD_MYTABLE]

	( 

	/*@P_OPMODE IS TO DEFINE DML INTERACTION MODE     

	INSERT=1, UPDATE=2, DELETE=3, RETRIVE=4*/    

		@P_OPMODE			INT				= 4,  

		@P_ID				NVARCHAR(50)	       = NULL,   

		@P_COLUM1			NVARCHAR(50)	       = NULL,

		@P_COLUM2			NVARCHAR(50)	       = NULL,

		@P_NAME				NVARCHAR(50)	        = NULL,	

		@P_EXECSTATUS		BIT				= 0    OUTPUT,    

		@P_ERRORNO			DECIMAL			= NULL OUTPUT,

		@P_ERRORMSG			NVARCHAR(2000)	= NULL OUTPUT    

	)

AS

	BEGIN

		BEGIN TRY

		IF @P_OPMODE = 1    

			BEGIN  

				IF @P_ID <> NULL

					BEGIN    

						INSERT INTO MYTABLE ...... 

					END 

			END

		ELSE IF @P_OPMODE = 2

				BEGIN

					UPDATE MYTABLE ...

				END	

		ELSE IF @P_OPMODE = 3	

				BEGIN

					DELETE FROM MYTABLE WHERE ..... 

					

				END

		ELSE	IF @P_OPMODE=4

				BEGIN	

                                       SELECT * FROM MYTABLE WHERE .... 

				END

				 SET @P_EXECSTATUS =1 

			END TRY

			BEGIN CATCH

				EXECUTE UP_LOG_PROCEDUREERROR /* TO LOG PROCEDURE ERRORS */

				SET @P_ERRORMSG = ERROR_MESSAGE()

				SET @P_ERRORNO = ERROR_NUMBER()

			END CATCH

	END

Something similar to this. Call this with P_OPMODE appropriately..... Also make sure you write your DML statements according to what you need

www.abhisheksur.com

Suneel161, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sagarp on: 3/25/2010 [Member] Bronze

0	single sp for insert update and delete create procedure ThreeOperationst_person(pin_ss varchar, pin_First varchar, pin_last varchar,pin_ss1 varchar, pin_First1 varchar, pin_last1 varchar,pin_first2 varchar) is begin insert into person values(pin_ss, pin_first, pin_last); update person set ssn= pin_ss1, Lname= pin_last1 where Fname = pin_First1; delete from person where Fname= pin_first2; commit; end ; method 2 1. get these pin_ss varchar, pin_First varchar, pin_last parameters. 2. get one more flag parameter flg_Operation. decide based on flag value create procedure ThreeOperationst_person(pin_ss varchar, pin_First varchar, pin_last varchar,flg_Operation varchar) is begin if flg_Operation = "Insert" insert into person values(pin_ss, pin_first, pin_last); if flg_Operation = "Update" update person set ssn= pin_ss1, Lname= pin_last1 where Fname = pin_First1; if flg_Operation = "Delete" delete from person where Fname= pin_first2; commit; end ; Thanks SagarP http://www.emanonsolutions.net http://emanonsolutions.blogspot.com/ Suneel161, if this helps please login to Mark As Answer. \| Alert Moderator

single sp for insert update and delete

create procedure ThreeOperationst_person(pin_ss varchar, pin_First varchar, pin_last varchar,pin_ss1 varchar, pin_First1 varchar, pin_last1 varchar,pin_first2 varchar)



is



begin



insert into person values(pin_ss, pin_first, pin_last);



update person set ssn= pin_ss1, Lname= pin_last1 where Fname = pin_First1;



delete from person where Fname= pin_first2;



commit;



end ;



 method 2



1. get  these pin_ss varchar, pin_First varchar, pin_last  parameters.



2. get one more flag parameter



flg_Operation. decide based on flag value



create procedure ThreeOperationst_person(pin_ss varchar, pin_First varchar, pin_last varchar,flg_Operation varchar)



is



begin



if flg_Operation = "Insert" insert into person values(pin_ss, pin_first, pin_last);



if flg_Operation = "Update" update person set ssn= pin_ss1, Lname= pin_last1 where Fname = pin_First1;



if flg_Operation = "Delete" delete from person where Fname= pin_first2;



commit;



end ;

Thanks
SagarP
http://www.emanonsolutions.net
http://emanonsolutions.blogspot.com/

Suneel161, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts