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

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

More SQL Server Interview Questions

what purpose does the model database serve? : Sql server database administration

0 Answers  


How to generate the Reports for the Database? I need an Example for it.Will we manually do this or else any script is there? Please let me know ASAP...

1 Answers   Cognizant,


How to Debug a Stored Procedure?

5 Answers   Allianz,


Why use cursor in sql server?

0 Answers  


How to change the ownership of a schema in ms sql server?

0 Answers  






What is resource governor?

0 Answers  


How do I determine how many instances of sql server are installed on a computer?

0 Answers  


Is foreign key a primary key?

0 Answers  


Can we linked SharePoint to a SQL database?

0 Answers   Sans Pareil IT Services,


What is relationship? What number of sorts of relationship are there?

0 Answers  


What is the purpose of the master database?

0 Answers  


How do you drop an index?

0 Answers  


Categories