Single stored procedure for update,delete and insert

Question

Hi, How to achieve this?

Pravin More · Answer

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.

Nethra R S · Answer

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

B M Suchitra · Answer

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?

Pravin More · Answer

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.

Nethra R S · Answer

Hi Pravin, Is that one stored procedure enough for the whole database?

Pravin More · Answer

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

B M Suchitra · Answer

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

Pravin More · Answer

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

Prabhu Raja · Answer

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

B M Suchitra · Answer

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?

Pravin More · Answer

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.

Prabhu Raja · Answer

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

Single stored procedure for update,delete and insert

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (12)