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)

how many triggers you can have on a table? : Sql server database administration

904

---

How do you make a trace?

955

---

What is the parse query button used for?

981

---

How to connect ms access to sql servers through odbc?

970

---

Explain transaction server auto commit?

988

---

What is save transaction and save point?

1119

---

Tell me what is a linked server?

987

---

What are subqueries in sql server?

1063

---

Tell me about the approaches which you used to counter the DI problems.

1268

---

what kind of lan types do you know? : Sql server database administration

923

---

What are a scheduled jobs?

908

---

What does it mean if @@cursor_row returns a negative number?

942

---

What is sql view?

954

---

what is database replicaion? What are the different types of replication you can set up in sql server? : Sql server database administration

925

---

ow to bring suspect mode datbase online from scratch

1762

---