sql server 2000 stored procedure

I have created a store procedure for updating the patient name in multiple tables. When I execute it, shows the error like

Server: Msg 170, Level 15, State 1,

Line 1 Line 1: Incorrect syntax near '='.

This is my code. How to recover it? Please help me

create procedure uppatname @pid varchar(150),@pname varchar(150)
as begin
  declare @i as integer
  declare @i1 as integer
  declare @ttnm  as varchar(100)
  declare @tblnam as varchar(100)

  drop table tbname
  SELECT IDENTITY(int, 1,1) AS RowNumber, table_name  
    INTO tbname  
    FROM information_schema.columns 
    WHERE column_name = 'pid' 
      AND table_catalog = 'hospital' 
      AND table_name NOT LIKE 'T%'

  SET @i = (select count(*) from information_schema.columns 
            where column_name='pid' and table_catalog='hospital' 
              and table_name not like 'T%')

  SET @i1 = 1

  WHILE @i1 <= @i
  BEGIN
     SET @tblnam = (select table_name  from tbname where rownumber = @i1)
     SET @ttnm = ('select  * from ' + @tblnam + 'where pid = ' + @pid)
     EXEC (@ttnm)
     SET @i1 = @i1 + 1
  END
END

What is the need for this task? Instead of storing denormalised redundant copies of the same name that you then have to manually keep up-to-date can't you store it in one place then join on it?

Some random comments about the code.

Why are you dropping and recreating the table tbname each time instead of just using truncate?

Although why are you using a permanent table for this at all? If you have concurrent executions of the stored procedure you could end up updating the wrong patient record with the wrong name. You could just use a #temp table or a @table variable

You don't need to do the SELECT COUNT(*)... and run the query again get the rowcount. You can just do @@rowcount

You should use quote_name around the table name and schema qualify it. As pid is not numeric it would need to be quoted in the query though actually you should use sp_executesql to allow you to parameterise pid

CREATE PROCEDURE uppatname @pid   VARCHAR(150), 
                           @pname VARCHAR(150) 
AS 
  BEGIN 
      DECLARE @i AS INTEGER 
      DECLARE @i1 AS INTEGER 
      DECLARE @dyn_sql AS NVARCHAR(4000) 
      DECLARE @tblnam AS SYSNAME 
      DECLARE @schema_name SYSNAME 

      DECLARE @tbname TABLE ( 
        RowNumber   INT IDENTITY(1, 1) PRIMARY KEY, 
        schema_name SYSNAME, 
        table_name  SYSNAME ) 

      INSERT INTO @tbname 
      SELECT TABLE_SCHEMA, 
             TABLE_NAME 
      FROM   INFORMATION_SCHEMA.COLUMNS 
      WHERE  COLUMN_NAME = 'pid' 
             AND TABLE_CATALOG = 'hospital' 
             AND TABLE_NAME NOT LIKE 'T%' 

      SET @i = @@ROWCOUNT 
      SET @i1 = 1 

      WHILE @i1 <= @i 
        BEGIN 
            SELECT @tblnam = table_name, 
                   @schema_name = schema_name 
            FROM   @tbname 
            WHERE  RowNumber = @i1 

           SET @dyn_sql = N'SELECT  * 
                           FROM ' + QUOTENAME(@schema_name) + '.' 
                                  + QUOTENAME(@tblnam) + ' 
                           WHERE pid = @pid';

            EXEC sp_executesql 
              @dyn_sql, 
              N'@pid VARCHAR(150)', 
              @pid=@pid 

            SET @i1 = @i1 + 1 
        END 
  END  

NB: You could also concatenate the whole script in a single query and execute it but that technique isn't 100% guaranteed so I haven't used it above.