Search code examples
sqlsql-serverssms

SQL Creating a procedure, but it doesn't see my tables

I created a new database called sample1. In it I created 3 tables: "address, contact and buyer"

CREATE TABLE address
(
    address_id int NOT NULL identity(1,1) primary key,
    street_name varchar(30)
);

CREATE TABLE contact
(
    contact_id int NOT NULL identity(1,1) primary key,
    phone_num varchar(20) NOT NULL,
    address_id int NOT NULL foreign key REFERENCES address(address_id)
);

CREATE TABLE buyer
(
    username char(20) NOT NULL primary key,
    name char(40) NOT NULL, 
    contact_id int NOT NULL foreign key REFERENCES contact(contact_id)
);

The tables were created successfully, now I'm trying to create a stored procedure as follows:

create procedure csc
(
    @username char(20),
    @address_id int,
    @contact_id int
)
as
set nocount on;
insert into [dbo].[buyer] values (@username);
insert into [dbo].[address_id] values (@address_id);
insert into [dbo].[contact_id] values (@contact_id);
go

But I get the following errors:

Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@username".

Msg 137, Level 15, State 2, Line 10
Must declare the scalar variable "@username".

Msg 137, Level 15, State 2, Line 11
Must declare the scalar variable "@address_id".

Msg 137, Level 15, State 2, Line 12
Must declare the scalar variable "@contact_id".

My questions / errors:

  1. Are my tables logical? Should I be using buyer username as the PK for address / contact ID (my teacher suggested this, but I think it's bad practice)

  2. When creating my procedure, I get red lines on [dbo].[buyer], [dbo]. [address_id], [dbo].[contact_id], can I ignore these?

  3. How in the world do I fix the errors?

Thanks in advance.

Solution

  • 1, are my tables logical? should I be using buyer username as the PK for address / contact ID (my teacher suggested this, but I think it's bad practice)

    It depends whether a buyer is always defined by their username and that won't get updated. This is an age old debate between natural and surrogate keys, you can have a google and find some pretty convincing arguments for either side. If you think it's bad practice then you'll need to be able to explain why you think this. I would certainly avoid using the char datatype unless you really wanted to use it, it will blank pad your data up to it's specified length.

    2, when creating my procedure, I get red lines on [dbo].[buyer], [dbo]. [address_id], [dbo].[contact_id], can I ignore these?

    That's because when SSMS last looked, those tables do not exist. If you press CTRL+SHIFT+R, you will refresh intelligence and it will check again. It'll still fail though because address_id nor contact_id are tables.

    3, how in the world do I fix the errors?

    You fix the table names. But once you've done that you'll reach another error as you aren't specifying what column your values should be inserted into and there are multiple it can use. The problem is that your code wants to insert your primary key columns but with no additional data - so really you need to complete your code so that all the required data is input and the columns they are inserting into are explicitly mentioned.

    e.g.

    insert into [dbo].[buyer] (username, name, contact_id) values (@username, @name, @contact_id);

    But.. you are inserting the contact row at the same time and you've declared it as an identity column, should you really know the value of this column already or should you be inserting the other values into the table and generating a new contact_id with it?

    Perhaps your procedure should really start

    create procedure csc(
    @username char(20),
    @name char(40),
    @street_name varchar(30),
    @phone_num varchar(20)
)

    And then build up your insert statements from that data. Something like:

    as
set nocount on;
declare @address_id int;
declare @contact_id int;

insert into [dbo].[address] (street_name) values (@street_name);
set @address_id = SCOPE_IDENTITY() ;

insert into [dbo].contact (phone_num, address_id) values (@phone_num, @address_id);
set @contact_id = SCOPE_IDENTITY() ;

insert into [dbo].[buyer] (username, name, contact_id) values (@username, @name, @contact_id);
go

    And to demo:

    exec csc 'andy','andy','Nice Road',42

select * from buyer;
select * from contact;
select * from address;
username             name                                     contact_id
-------------------- ---------------------------------------- -----------
andy                 andy                                     3

(1 row affected)

contact_id  phone_num            address_id
----------- -------------------- -----------
3           42                   3

(1 row affected)

address_id  street_name
----------- ------------------------------
3           Nice Road

(1 row affected)