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 are the types of indexing?

1073

---

Is sql server 2016 free?

926

---

Explain time data type in sal server 2008?

915

---

How to select some specific rows from a table in ms sql server?

992

---

Give main differences between "Truncate" and "Delete".

1087

---

How to set database to be read_only in ms sql server?

995

---

Differentiate between a having clause and a where clause.

958

---

In how many ways you can invoke ssrs reports?

137

---

Why use triggers?

1002

---

What is normalization of database? What are its benefits?

1018

---

How to connect to SQL Azure Database by using sqlcmd?

136

---

What is normalization and denormalization in sql server?

998

---

What happens if null values are involved in comparison operations?

959

---

Why should we go for stored procedures? Why not direct queries?

1087

---

Can you import Microsoft Excel data to SSRS?

132

---