Single stored procedure for update,delete and insert

Hi Nethra ,
  i gave it in thread started by You here.... and  parameters of sp is enough no need of extra columns

http://www.c-sharpcorner.com/Forums/Thread/146676/single-stored-procedure-for-insert-update-deleteis-it-g.aspx

still m pasting it here....

@valueClause varchar(max),
 @SetClause varchar(max),
 @whereClause varchar(max),
 @Table_name varchar(max),
 @case  int  -- will be 1 for insert,2 for update ,3 for Delete

actually , these both threads are same(quetion) 1 is strated by you and another by Suchitra...its confusing...n you are toggling between these two.....   

i hope you will able to solve your problem with the help of my reply....

Thanks
Pravin.

@Pravin,
You have missed out declaring column names in your dynamic SP.

Hi all,

How to write this dynamic stored procedure in dataaccess layer? instead of writing in database? And how to call this and pass parameters?

yes nethra.....its enough for 1 database as you are passing table names and colmn name as parameter dynamically......

 and if you want it for more than database then  pass table name as databasename..tablename

thanks,
Pravin.

Hi Pravin,

Is that one stored procedure enough for the whole database?

hi suchitra pass that parameter but dont use it insert it like

set @qry='insert into table_name  values('+ProcessName +','+ProcessCode+','+@Description+')'

exec(@qry)


Thanx
Pravin

Hi all,

I tried to write a procedure for update and insert like this.
create procedure sp_processmaster
@ProcessID int,
@ProcessName varchar(50),
@ProcessCode varchar(20),
@Description varchar(MAX)
 here i have 4 parameters... for insert i need 3 parameters since processid is autoincremented.. I get and error here.. For update it works fine

ok suchitra if want id of previously inserted record here is solution....

   when u are calling sproc for insert u pass @valueClause....so now pass @whereClause too which contain values eith coulmn u r inserting.....then immidiatly after insert query execute select query i.e 

   set @qry='insert into '+@Table_name+' values('+@valueClause+')';
        exec(@qry)

   set @qry1='select id from '+@Table_name+'  where '+@whereClause+' '

    int @newID=exec(@qry1)

   now u have newID then just use it to insert to another table like......

    set @qry2='insert into '+@Table_name2+' values('+@valueClause+')';
        exec(@qry2)


hope this will solve ur problem.....

Thanx,
Pravin





 

Hi Suchitra,

  Use SCOPE_IDENTITY() Function for your purpose.

  Consider,  The ID Column of first table is AutoIncrement. You Can do this by,


  Insert Into TableName1 Values("Name");

  DECLARE @ID Integer
 
  Select ID = @ID from TableName1 Where ID = SCOPE_IDENTITY()

 then,

  Insert Into TableNAme2 Values (@ID)  --Store the ID of Previous table

--------------------------------------------------------------------------
If this Post Helps you, then mark as "Correct Answer"
Thank You

Thanks Praveen.

I also wanted to know how a single stored procedure will help to save data in many tables... I want the id of the previous table to store in another table.. In single stored procedure how do i do it?

hi suchitra, your question is not clear......but stiil if u want to do insert,update,delete operations 1 at a time by taking culumn values dyanamically as a parameter to sproc...here is the way......

  create procedure InDelUp
  @valueClause varchar(max),
  @SetClause varchar(max),
  @whereClause varchar(max),
  @Table_name varchar(max),
  @case  int  -- will be 1 for insert,2 for update ,3 for Delete

  as 
  Begin
 declare  @qry varchar(max)
  if(@case=1)
  begin
  set @qry='insert into '+@Table_name+' values('+@valueClause+')';
  exec(@qry) 
  end

    if(@case=2)
  begin
  set @qry='Update '+@Table_name+' set '+@SetClause +' where '+@whereclause+'';
  exec(@qry) 
  end

    if(@case=3)
  begin
  set @qry='Delete from '+@Table_name+'  where '+@whereclause+' ';
  exec(@qry) 
  end

 
  End 

this is way...pass parameter accordingly.......u can change parameter n query according to ur requirment.....
hope it solve ur problem
if u dont need perticular parameter in any case pass it anything may be just blank string as we r not going to use it.....


Thanx,
Pravin.

Hi,

Try this

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

And use this Procedure Like

--To Insert
EXEC PROCNAME  1,'Bangladesh',1

--To UPDATE
EXEC PROCNAME  1,'Bangladesh',2

--To DELETE
EXEC PROCNAME  1,NULL,0

---------------------------------------------------
If this post helps you, then mark as "Correct Answer"
Thank You