Must declare the scalar variable table

1
votes

I have the following SQL:

DECLARE @HospitalReport TABLE (Registrator VARCHAR (20))

INSERT INTO @HospitalReport (Registrator) 
VALUES("64")

SELECT 
    @HospitalReport.Registrator 
FROM 
    @HospitalReport

IF Registrator > 0
BEGIN
    SELECT 
        Database.dbo.Users.Firstname, Database.dbo.Users.Lastname 
    FROM 
        StradaAnv.dbo.Anvandare 
    WHERE 
        Id = Registrator

    IF Firstname != NULL AND Lastname != NULL
    BEGIN
        UPDATE @HospitalReport 
        SET Registrator = Firstname + ' ' + Lastname 
        WHERE Registrator = Registrator
    END 
END

SELECT * FROM @HospitalReport

When I run this code, I get the following error:

Msg 137, Level 16, State 1, Line 9
Must declare the scalar variable "@HospitalReport"

What I see, I already have declared @HospitalReport as a table?

1
sqlsql-servertsql
use aliases for tables – Ivan Starostin
Also, you cannot compare against NULL with the standard equality and non-equality operators - you must use FirstName IS NULL (or IS NOT NULL) – marc_s

1 Answers

4
votes

Don't split everything out into procedural steps. Tell the system what you want, not how to do it:

DECLARE @HospitalReport TABLE (Registrator VARCHAR (20))

INSERT INTO @HospitalReport (Registrator) 
VALUES("64")

UPDATE H  
SET Registrator = Firstname + ' ' + Lastname
FROM
    @HospitalReport H
        INNER JOIN
    StradaAnv.dbo.Anvandare A
        ON
           H.Registrator = A.Registrator
WHERE A.Firstname IS NOT NULL AND
      A.Lastname IS NOT NULL

SELECT * FROM @HospitalReport

I.e. I'm not first querying the table. Then seeing whether particular columns are not null1. Then deciding whether or not to perform an update. I'm describing the entire operation in a single query and then letting the optimizer work out how best to perform this task.

1Which, as shown above, should be done using the IS NULL operator rather than != since NULL is neither equal not not equal to NULL