Must declare the scalar variable "@id" error with stored procedure

Question

I got this procedure with a cursor

CREATE PROCEDURE usp_Inventario
AS
    DECLARE @Id int, @Nombre varchar(255), @precio decimal, @st int, @inv int
    SET @inv = 0

    DECLARE cproducto CURSOR FOR
        SELECT P.ProductID, P.ProductName, P.UnitPrice, P.UnitsInStock
        FROM Products P

and

OPEN cproducto

FETCH cproducto INTO @id, @Nombre, @precio, @st

WHILE (@@FETCH_STATUS = 0)
BEGIN
    PRINT Str(@id) + space(5) + str(@nombre) + space(5) +  Str(@precio) + space(5) + Str(@st)

    SET @inv += @st

    FETCH cproducto INTO @id, @Nombre, @precio, @st
END

CLOSE cproducto
DEALLOCATE cproducto 

PRINT 'Inventario de Productos:' + Str(@inv)

I get this error

Must declare the scalar variable "@id"

multiple times, could someone please tell me what I am doing wrong?

Is your database collation case sensitive? You declared `@Id` with uppercase but use `@id`. — 1010, Nov 29 '18 at 02:52
The query you've pasted would not produce that error (so long as the top part and the bottom part are in the same scope)... — ZLK, Nov 29 '18 at 03:16
@Batam what collation are you using? `SELECT collation_name FROM sys.databases WHERE database_id = DB_ID();` — Dan Guzman, Nov 29 '18 at 03:22
see here: https://stackoverflow.com/a/6559516/4020264, maybe your server's collation is case sensitive. — 1010, Nov 29 '18 at 04:11
check may be its case sensitive, you declared as `@Id` and using as `@id` — Deepak Sharma, Nov 29 '18 at 05:23
It's not case sensitive i can tell. Also just to prove it i tried it right now, same error. — Bata, Nov 30 '18 at 01:08

score 0 · Answer 1 · answered Nov 29 '18 at 02:44

0

At the very least, you need begin/end:

CREATE PROCEDURE usp_Inventario
AS
BEGIN
    DECLARE @Id int, @Nombre varchar(255), @precio decimal, @st int, @inv int;
    SET @inv=0;
    DECLARE cproducto CURSOR FOR
    SELECT  P.ProductID, P.ProductName, P.UnitPrice, P.UnitsInStock
    FROM Products P;

    . . .
END;

answered Nov 29 '18 at 02:44

Gordon Linoff

1,242,037
58
646
786

It still says: Must declare the scalar variable "@id" :( – Bata Nov 29 '18 at 02:59
@Bata . . . `@id` is declared in the stored procedure. Is all your code between the `begin` and `end`? – Gordon Linoff Nov 29 '18 at 13:14
Yes that's all of it – Bata Nov 30 '18 at 01:06

Must declare the scalar variable "@id" error with stored procedure

1 Answers1