How to write stored procedure to update the data in 10 tables

How to write stored procedure to update the data in 10
tables

Question Posted / tester

4 Answers
13786 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

How to write stored procedure to update the data in 10 tables..

Answer / mohit johri

Hey Vaishaili,
The query you wrote first of all does not require a dynamic
string to be constructed and then executed. The problem
with this is you are defeating the purpose of a stored
procedure.

A stored procedure is basically used to make the execution
faster as the code is kept in pre-complied mode.

Here what you are doing is you are giving an 'EXEC'
statement to execute the query which means that
the 'update'statements are not kept in pre-compiled mode.
They will first compile and then execute just like a normal
SQL statement.

Hence you should directly write the 'UPDATE' statements so
that it should only be executed and not compiled as they
are already kept in a pre-compiled mode.

The procedure can be best written as follows:

CREATE PROCEDURE <<procName>>
(
@param1 varchar(20),
@param2 varchar(20)
)
AS
UPDATE <<tableName1>> SET <<colName1>> = @param1 WHERE
<<colName2>> = @param2

UPDATE <<tableName1>> SET <<colName1>> = @param1 WHERE
<<colName2>> = @param2

..
..
..
..

UPDATE <<tableNameN>> SET <<colName1>> = @param1 WHERE
<<colName2>> = @param2

Exec(@sql)

Is This Answer Correct ?

15 Yes

3 No

How to write stored procedure to update the data in 10 tables..

Answer / mohit johri

Kindly ignore the last statement in my posted answer which
says 'Exec(@sql)' it was wrongly copied

Is This Answer Correct ?

9 Yes

2 No

How to write stored procedure to update the data in 10 tables..

Answer / vaishali

create procedure proc_name
{
@para1 varchar(20),
@para2 varchar(20),
}
AS
Declare @sql varchar(8000)
Set @sql="update table1 set col1='value' where
col2='"+@para1+"'\n"
Set @sql=@sql+ "update table2 set col1='value' where
col2='"+@para2+"'\n"

Exec(@sql)

Is This Answer Correct ?

10 Yes

11 No

How to write stored procedure to update the data in 10 tables..

Answer / jaipal

CREATE PROCEDURE <<procName>>
(
@param1 varchar(20),
@param2 varchar(20)
)
AS
UPDATE <<tableName1>> SET <<colName1>> = @param1 WHERE
<<colName2>> = @param2

UPDATE <<tableName1>> SET <<colName1>> = @param1 WHERE
<<colName2>> = @param2

..
..
..
..

UPDATE <<tableNameN>> SET <<colName1>> = @param1 WHERE
<<colName2>> = @param2

Is This Answer Correct ?

6 Yes

7 No

Post New Answer