How to fix that procedure in sql

I created procedure which count not null rows in the column, but query throws errors: @tableName is not declared and invalid object name tempTable. I don’t know why code throws that errors, because all variables are declared.

Msg 1087, Level 16, State 1, Procedure getLenCol, Line 7 [Batch Start Line 0]
Must declare the table variable "@tableName".
Msg 208, Level 16, State 1, Line 11
Invalid object name 'tempTable'.


CREATE OR ALTER PROC getLenCol
      @tableName  varchar(255),
      @colName varchar(255)
    as
      DECLARE @tempTable Table(smth varchar(255));
      DECLARE @query varchar(255)
      insert into @tempTable(smth) select @colName from @tableName where @colName is not null
      exec (@query)
      select @@ROWCOUNT
    GO
    exec getLenCol 'users','name'

Also when I make that program in another way, that code throw Msg 1087, Level 15, State 2, Line 11 error.

Must declare the table variable "@tempTable".

CREATE OR ALTER PROC getLenCol
  @tableName  varchar(255),
  @colName varchar(255)
as
  DECLARE @tempTable Table(smth varchar(255))
  DECLARE @query varchar(255)
  SET @query = concat('insert into @tempTable(smth) select ',@colName,' from ',@tableName,' where ',@colName,' is not null');/*@colName from @tableName where @colName is not NULL*/
  exec (@query)
  select @@ROWCOUNT
GO
exec getLenCol 'users','name'

Is it a way to fix that error?

Answer

Obviously, your code is subject to SQL injection attacks — as the comments on the question have explained.

But your issue is the scoping rules around your table variable. You can fix that by using:

set @query = concat('select ', @colName, ' from ', @tableName, ' where ', @colName,' is not null');

insert into @tempTable (smth)
    exec(@query);

I don’t think there is any way around the SQL injection vulnerabilities for the logic you have suggested. However, your code is so non-sensical that I doubt that it is really representative of your actual code.