Problem in creating a stored Procedure

Question

I am trying to create a stored database that serves to check if my table is empty, and if it is not returning the value with the higher Id. But I’m not getting it.

CREATE PROCEDURE spConsulta
@NameTable VARCHAR (50)
AS
BEGIN
    SELECT Value
    FROM @NameTable
    WHERE Id = (SELECT MAX(Id) FROM @NameTable);

    IF @@ROWCOUNT = 0
        RETURN 0 
END

Errors.

Msg 1087, Level 16, State 1, Procedure spConsult, Line 6 [Lot Start Line 0] It is necessary to declare the table variable "@Nametable". Msg 1087, Level 16, State 1, Procedure spConsult, Line 7 [Lot Start Line 0] It is necessary to declare the table variable "@Nametable".

Would anyone know how to solve this problem ? Apparently the problem is in @Nametable, I am doing this way because I am trying to use this stored Procedure for all tables, because all tables have the same structure, apparently it is not working. Would anyone know any alternative way to do this ?

Answer 1

What you forgot was to declare the variable, the console itself showed this: Msg 1087, Nível 16, Estado 1, Procedimento spConsulta, Linha 6 [Linha de Início do Lote 0] É necessário declarar a variável de tabela "@NameTable". Msg 1087, Nível 16, Estado 1, Procedimento spConsulta, Linha 7 [Linha de Início do Lote 0] É necessário declarar a variável de tabela "@NameTable".

Solution:

CREATE PROCEDURE spConsulta
    @NameTable varchar(50)
AS      
BEGIN 
    SELECT Value,Id
    FROM NameTable
    WHERE Id = MAX(Id)
    IF @@ROWCOUNT = 0
        RETURN 0 
    END

Useful link:

https://docs.microsoft.com/pt-br/sql/t-sql/language-elements/variables-transact-sql?view=sql-server-2017

https://docs.microsoft.com/pt-br/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-2017

https://docs.microsoft.com/pt-br/sql/t-sql/language-elements/declare-local-variable-transact-sql?view=sql-server-2017