Must declare the scalar variable with parameter

Question

I want to create a procedure to update a table but I'm getting this error:

(68,1): SQL72014: .Net SqlClient Data Provider:
Message 137, Level 16, State 1, Procedure updatePatient, Line 11
Must declare the scalar variable "@pPatient".

Here is the procedure I am trying to create:

CREATE PROCEDURE [dbo].[updatePatient]
    @pPatient PatientTableType readonly 
AS 
BEGIN
    UPDATE Patient
    SET Patient.Cel = @pPatient.Cel,
        Patient.Address = @pPatient.Address,
        Patient.NamePatient = @pPatient.NamePatient,
        Patient.Phone = @pPatient.Phone
    WHERE Patient.idPatient = @pPatient.idPatient 
END

Here is the type that I created:

CREATE TYPE [dbo].[PatientTableType] AS TABLE 
       (
           [idPatient] SMALLINT NOT NULL,
           [NamePatient] VARCHAR(250) NOT NULL,
           [Cellular] INT NULL,
           [Phone] INT NULL,
           [Address] VARCHAR(250) NULL
       );

Here is the table that I want to update:

CREATE TABLE [dbo].[Patient] 
(
    [idPatient] SMALLINT NOT NULL,
    [NamePatient] VARCHAR(250) NOT NULL,
    [Cel] INT NULL,
    [Phone] INT NULL,
    [Address] VARCHAR(250) NULL,

    PRIMARY KEY CLUSTERED ([idPatient] ASC)
);

What did I do wrong? How can I fix it?

Thanks in advance!

Is that really your procedure code? The error messages suggests the issue is on line 11 but you don't have that many lines. — DavidG, May 01 '17 at 00:31
sorry its because I've formatted, line 11 = where Paciente.idPaciente = @pPaciente.idPaciente — Daniel Andrade Ripardo, May 01 '17 at 00:45
You can not access values of table type variable by using dot. You need to use join between the patient table and table type variable. Have a look at http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match — Chetan, May 01 '17 at 00:49
thanks for your comment @ChetanRanpariya it realy helps, but i solved the problem puting [@Patient] between square brackets and it works. If it isn't a good way say it please... — Daniel Andrade Ripardo, May 01 '17 at 00:58
Please note that you can post an Answer to your own Question. After 48 hours you can then accept your Answer. This will not give you any points, but it will show those reading this page what you found to be the solution, which may help them with any problems they might have. Your Answer can still be upvoted. The 48 hours wait is so that other people have a chance to post a potentially better Answer. Accepting an Answer will also remove your Question from StackOverflow's list of unanswered Questions. Please read https://stackoverflow.com/help/accepted-answer for more information. — toonice, May 01 '17 at 06:03

score 0 · Answer 1 · 2017-05-01T11:35:55.367

Hope this works

CREATE PROCEDURE [dbo].[updatePatient]
(
    @pPatient PatientTableType READONLY 
)
AS 
BEGIN
   IF EXISTS(SELECT 1 FROM @pPatient)
   Begin
    UPDATE Patient
    SET Patient.Cel = p.Cellular,
        Patient.Address = p.Address,
        Patient.NamePatient = p.NamePatient,
        Patient.Phone = p.Phone
        FROM @pPatient p
    WHERE Patient.idPatient = p.idPatient 
    END
END

Executed with Sample data

DECLARE @PatientTableType AS PatientTableType
INSERT INTO @PatientTableType
SELECT 1,'ABC',985565,4242,'Hankong'
EXECUTE [updatePatient] @PatientTableType

SELECT * FROM [Patient]

Must declare the scalar variable with parameter

1 Answers1