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 to insert data with null values?

1078

---

How do I determine how many instances of sql server are installed on a computer?

1052

---

List all the types of user-defined functions?

1177

---

What do you understand by user-defined function in the sql server and explain the steps to create and execute a user-defined function in the sql server?

1015

---

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

1179

---

explain what is a deadlock and what is a live lock? How will you go about resolving deadlocks? : Sql server database administration

937

---

What is a non clustered primary key?

972

---

What will be the value of @@fetch_status if a row that was a part of the cursor resultset has been deleted from the database after the time the stored procedure that opened the cursor was executed?

1189

---

how you can deploy an ssrs report?

185

---

What are the aggregate and scalar functions?

1128

---

What is a raid and what are different types of raid configurations?

1321

---

Do you know the different ddl commands in sql?

1097

---

What is the use of commit?

1056

---

What is the difference between MVC and Teir Architecher? Plz explain with Layyered Programming example...? Thanks

2132

---

How to generate create procedure script on an existing stored procedure?

1060

---