How to write stored procedure to update the data in 10
tables
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
List all types of constraints in sql server?
What does it mean to manipulate data?
What are the 2 types of classifications of constraints in the sql server?
How many triggers you can have on a table?
How To Change Column Ordinal Position in SQL Server 2005 using Query i.e I Want To Add Column at Particular Ordinal Position in SQL Server 2005
How much memory that we are using in Logshipping Concept?
What are different types of table joins?
What are different types of raid levels?
You have several tables, and they are joined together for querying. The tables contain both clustered indexes and non clustered indexes to optimize performance, how should you distribute the tables and their indexes onto different file groups?
What happens to a trigger with multiple affected rows?
What is an Index?
Do you know what guidelines should be followed to help minimize deadlocks?
Oracle (3259)
SQL Server (4518)
MS Access (429)
MySQL (1402)
Postgre (483)
Sybase (267)
DB Architecture (141)
DB Administration (291)
DB Development (113)
SQL PLSQL (3330)
MongoDB (502)
IBM Informix (50)
Neo4j (82)
InfluxDB (0)
Apache CouchDB (44)
Firebird (5)
Database Management (1411)
Databases AllOther (288)