How to write the stored procedure query

Question

I am trying to create a stored procedure to insert a new record into an INVOICE table

Details are:

INVOICE (INV_NUMBER [PK, int], CUS_CODE [FK, int], INV_DATE [Date/time])
CUSTOMER (CUS_CODE [PK], LNAME, FNAME, INITIAL, AREACODE, PHONE, BALANCE)

How do I go about writing the stored procedure query to insert a new row in INVOICE when there is a FK and int columns?

This is what i did, but is incorrect:

CREATE PROC spNewCustomer_INVOICE
AS
BEGIN
    DECLARE @INV_NUMBER VARCHAR(50);
    DECLARE @CUS_CODE VARCHAR(50);
    DECLARE @INV_DATE VARCHAR(50);
AS 
    INSERT INTO INVOICE
    VALUES (@INV_NUMBER, @CUS_CODE, @INV_DATE)

Calling it:

EXEC spNewCustomer_INVOICE '2018', '20018', '2018-08-17'

Thanks for any help!

This is not the correct way of doing it. I am getting the following error messages:Msg 102, Level 15, State 1, Procedure spNewCustomer_INVOICE, Line 256 Incorrect syntax near '='. Msg 156, Level 15, State 1, Procedure spNewCustomer_INVOICE, Line 260 Incorrect syntax near the keyword 'AS'. Msg 137, Level 15, State 2, Procedure spNewCustomer_INVOICE, Line 264 Must declare the scalar variable "@INV_NUMBER". — Hifza Rahim

marc_s marc_s · Accepted Answer · 2018-08-18T07:42:36

First of all - you need to create parameters for your stored procedure - not internal variables.

Secondly, I would strongly recommend using the most appropriate datatype for each parameter - not just VARCHAR(50) for everything. Your table's columns are of type INT and DATETIME - use those types for your parameters!

And thirdly, I recommend to always explicitly specify the columns of the target table you're inserting into.

So all in all - try this code:

CREATE PROCEDURE NewCustomerInvoice
    @INV_NUMBER INT,
    @CUS_CODE INT,
    @INV_DATE DATETIME
AS
BEGIN
    INSERT INTO INVOICE (INV_NUMBER, CUS_CODE, INV_DATE)
    VALUES (@INV_NUMBER, @CUS_CODE, @INV_DATE);
END;

Then call this procedure with this command:

EXEC NewCustomerInvoice 
         @INV_NUMBER = 2018, 
         @CUS_CODE = 20018, 
         @INV_DATE = '20180817';

How to write the stored procedure query

1 Answers