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 |
what purpose does the model database serve? : Sql server database administration
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...
How to Debug a Stored Procedure?
Why use cursor in sql server?
How to change the ownership of a schema in ms sql server?
What is resource governor?
How do I determine how many instances of sql server are installed on a computer?
Is foreign key a primary key?
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?
What is the purpose of the master database?
How do you drop an index?