How to write stored procedure to update the data in 10
tables
- 4 Answers
- 13189 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
System requirements for sql server 2005 express edition?
what is a join and explain different types of joins? : Sql server database administration
List some of the rules that apply to creating and using a ‘view’
How to turn off warning messages during php execution?
Does an index slow down updates on indexed columns?
What is implicit cursors?
What is the difference between sql server 2000 object owner and sql server 2005 schema? : sql server database administration
How many clustered indexes can be created on a table? I create a separate index on each column of a table. what are the advantages and disadvantages of this approach?
What are the types of containers in ssis?
What is co-related sub query?
How to define and use table alias names in ms sql server?
How to create function without parameter in sql server?
How to convert binary strings into integers in ms sql server?
what is sql server? : Sql server database administration
How to convert numeric expression data types by assignment operations?
