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)

Answer / mohit johri

Kindly ignore the last statement in my posted answer which
says 'Exec(@sql)' it was wrongly copied

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)

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

What are dml (data manipulation language) statements in ms sql server?

0 Answers  

---

How do I find information about the install locations for the various instances running on a computer?

0 Answers  

---

what authentication modes does sql server support? : Sql server database administration

0 Answers  

---

Why are views required in the sql server or in any other database?

0 Answers  

---

Is sql server is free?

0 Answers  

---

What is Stored Procedure? What is Views in sql server? Difference between a User Defined Function and a Stored Procedure Difference between a primary key and a unique key? What is a join and explain different types of joins. Difference between temp table and table variable Difference between Triggers and Stored Procedures Difference between UNION ALL Statement and UNION What is COALESCE / Why do we use COALESCE? Why we use SET ROWCOUNT in Sql How many clustered index can have a table How many types of local tables in SQL SERVER Difference between DELETE and TRUNCATE What is Aggregate Functions? What is Row_Number()? What are Ranking Functions? What is NOLOCK? What is CTE? What are the Advantages of using CTE? What is the STUFF function and how does it differ from the REPLACE function? What are the difference between clustered and a non-clustered index? What are the different index configurations a table can have? Difference between a HAVING CLAUSE and a WHERE CLAUSE? Difference between SET and SELECT Provide all the built in string function of SQL SERVER Difference between char and varchar data types Define candidate key, alternate key, composite key. What are constraints? Explain different types of constraints. What is a self join? Explain it with an example. How will you convert table row to a column comma separated value

4 Answers   ACS,

---

Explain triggers in sql?

0 Answers  

---

What is query cost in sql server?

0 Answers  

---

What are subqueries in sql server? Explain its properties.

0 Answers  

---

Differences between logshipping and mirroring

1 Answers   Microsoft, Syntel,

---

What is ems sql management studio? : sql server management studio

0 Answers  

---

What is trigger and different types of Triggers?

0 Answers   QuestPond,

---