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

Posted by Suneel161 under Regular Expressions on 3/10/2010 | Views : 1607 | Status : [Member] | Replies : 3
hiii
I want to create a single sp for insert update and delete.And how to call in programatically..




Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response