I have an entity named Country with fields: CountryID, CountryName where CountryID is a primary key. And i like to create a general stored procedure that does INSERT, UPDATE and DELETE operations. How can i do that?

Recommended Answers

you want a single stored procedure to do all of that?

i would advise to separate each out

Jump to Post

dickersonka is right...
Separated them into each procedure.

Jump to Post

All 7 Replies

you want a single stored procedure to do all of that?

i would advise to separate each out

dickersonka is right...
Separated them into each procedure.

HI
i think this is help ful for u


This is for insert

DELIMITER $$


CREATE

              /*[DEFINER = { user | CURRENT_USER }]*/
  
              TRIGGER `saurav`.`aa` BEFORE INSERT
   
             ON `saurav`.`authortrigger`
    
             FOR EACH ROW BEGIN
    
             insert into authortrigger_copy(AUTHID,AUTHORFN,AUTHORLN)


Delete TRIGGER

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `saurav`.`aa` BEFORE INSERT
    ON `saurav`.`authortrigger`
    FOR EACH ROW BEGIN
    delete from authortrigger_copy;

    END$$

DELIMITER ;

HI TRY WITH MY BELOW SAMPLE CODE:

CREATE PROCEDURE PROCNAME 	
@CountryId integer ,
@CountryName VARCHAR(200) ,
@nType integer
AS
BEGIN
IF @nType=1
INSERT INTO Country VALUES(@CountryId,@CountryName);
ELSE IF @nType=2
UPDATE Country SET CountryName=@CountryName WHERE CountryId=@CountryId;
ELSE IF @nType=0
DELET FROM Country WHERE CountryId=@CountryId
END

NOW RUN BY BELOW COMMAND:

--To Insert
EXEC PROCNAME  1,'Bangladesh',1
--To UPDATE
EXEC PROCNAME  1,'Bangladesh',2
--To DELETE
EXEC PROCNAME  1,NULL,0

THE ABOVE CODE IS A SAMPLE CODE. YOU HAVE TO MODIFY IT.
LET ME KNOW IF YOU DIDN'T Get Me.

my co database designer i have a problem on mysql
pliss give me a sample code on edit update delete and select sample code for MS SQL

ALTER PROCEDURE ruff_stor
@cid int,
@fname varchar(10),
@lname varchar(10),
@city varchar(10),
@pin varchar(10),
@type varchar(10)

AS
begin
set nocount on

if @type = 'Insert'
begin
insert into ruff(fname,lname,city,pin) values (@fname,@lname,@city,@pin)
end

else if @type = 'Update'
begin
update ruff set fname=@fname,lname=@lname,city=@city,pin=@pin where cid=@cid
end

else if @type = 'Delete'
begin
delete from ruff where cid=@cid
end
End
return

We appreciate your willingness to contribute but please check the dates on posts before you respond to them. This thread is several years old and it is likely that the original poster (OP) no longer needs help with this issue. Reviving this thread pushes more recent threads further down the list. If you continue to revive old threads you may be hit with an infraction.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.