How to write stored procedure to update the data in 10
tables
- 4 Answers
- 12589 Views
- I also Faced
- E-Mail Answers
Answer Posted / 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 |
Post New Answer View All Answers
What are cursors and when they are useful?
What is the difference between a function and a trigger?
What is trigger and different types of Triggers?
What are the properties of the transactions?
How do I find the sql server instance name?
What is log shipping?
What is the purpose of the tempdb database?
What are user-defined functions (udfs) in sql server?
How to create nested stored procedure?
What is the order in which the sql query is executed?
what types of replication are supported in sql server? : Sql server database administration
Why we need to use secondry database file? though, we can do same work using primary database file also.
What do I need to start working with sql studio? : sql server management studio
What is indexing a document?
What are “phantom rows”?
