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)

What is the architecture of ms sql reporting service?

934

---

What is 'write-ahead log' in sql server 2000 ?

999

---

How to replace null values in expressions using isnull()?

1058

---

What is store procedure?

1016

---

John exports information periodically from a microsoft sql server database to an oracle database what is the best way to do this?

899

---

What is the purpose of object explorer and its features? : sql server management studio

1137

---

What are the advantages dts has over bcp?

952

---

What are database states in ms sql server?

1126

---

What is bookmark link in ssrs?

234

---

How do I install sql server?

854

---

What do you mean by cardinality?

856

---

How to create a local temporary stored procedure?

954

---

What is triggers and its types?

961

---

How to create “dependant” parameter “make, model, year”

124

---

What are the tables in sql?

1044

---