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)

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)

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

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