Error creating update procedure

Question

I am currently working on an update procedure. I have checked other stack posts, and Youtube, but I can't seem to run the code. Here's one code that I have found and tried to make it work:

UPDATE db_owner
SET Street = ISNULL(@Street, street), 
WHERE Street = @Street

I get these errors:

Invalid object name db_Owner
Invalid Column name street
Must declare the scalar variable "@Street"

My tables are:

dbo.Appointment
dbo.Owner
dbo.Pet
dbo.Vet

My insert procedures are:

dbo.Spinsert_dbo_appointment
dbo.Spinsert_dbo_owner
dbo.Spinsert_dbo_vet
dbo.Spinsert_dbo_pet

So, you have no table `db_owner` and you're trying to update a table called `db_owner`. The error message seems pretty straightforward. Also, even if you put the right table name in (`dbo.Owner`?) your update statement isn't going to actually do anything. You're setting street to @street where the value of street is already @street. — Tom H, Dec 16 '17 at 15:22
Thats what I thought. When I typed out dbo.owner it said invalid object name. Also thank you for the feedback. I just learned T-SQL yesterday so I tend to make a lot of mistakes. Is there something I could do to make it actually do something like change the street address? — Trush Patel, Dec 16 '17 at 15:26

Ilyes · Accepted Answer · 2017-12-17T20:52:09.567

I would say do not use such names, and you really don't have a table called db_owner it's a database role:

UPDATE dbo.owner
SET Street=ISNULL(@Street, street) 
WHERE Street=@Street

Now for the error Must declare the scalar variable "@Street" it's because you don't declare the variable:

DECLARE @Street DataType; -- declaration of the variable @Street
SET @Street = Value; -- Set a value to the variable @Street
UPDATE dbo.owner
    SET Street=ISNULL(@Street, street) 
    WHERE Street=@Street

score 0 · Answer 2 · answered Dec 16 '17 at 15:23

you are getting this error because you used db_owner as table name instead of dbo.owner.

Also, declare the variable @street before using it.

Like this

DECLARE @Street VARCHAR(50)
UPDATE dbo.owner
SET Street=ISNULL(@Street, street), 
WHERE Street=@Street

Note : There is a system role is SQL Server called db_owner

Error creating update procedure

2 Answers2