Database Scripts

Best practice for production apps is to use REST as the data layer to access data and not directly integrate to SQL using the SQL data provider. The SQL data provider will be squiggled in blue to indicate it is not recommended, together with a message to use REST instead. See REST endpoints from Azure SQL for more information.

The following Azure SQL scripts create the customer table and store the procedures used in the examples in this section. These scripts should be executed against an existing database in your Azure SQL environment.

Create Customer Table

The following script creates a sample customer table in Microsoft Azure SQL.

createCustomers.sql

CREATE TABLE customers (
    id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    first_name NVARCHAR(50) NOT NULL,
    last_name NVARCHAR(50) NOT NULL,
    email NVARCHAR(100) UNIQUE NOT NULL,
    phone_number NVARCHAR(15) UNIQUE,
    address_line1 NVARCHAR(100) NOT NULL,
    address_line2 NVARCHAR(100),
    city NVARCHAR(50) NOT NULL,
    state CHAR(2) NOT NULL DEFAULT 'WA',
    zip_code CHAR(5) NOT NULL,
    country NVARCHAR(50) NOT NULL DEFAULT 'USA'
);

Insert sample records in the Customer table

The following script inserts sample customers into the table.

insertCustomer.sql

INSERT INTO customers (first_name, last_name, email, phone_number, address_line1, address_line2, city, zip_code)
VALUES
    ('John', 'Doe', '[email protected]', '206-555-0101', '123 Main St', 'Apt 4B', 'Seattle', '98101'),
    ('Jane', 'Smith', '[email protected]', '360-555-0102', '456 Pine St', NULL, 'Bellevue', '98004'),
    ('Michael', 'Johnson', '[email protected]', '425-555-0103', '789 Oak St', NULL, 'Redmond', '98052'),
    ('Emily', 'Davis', '[email protected]', '253-555-0104', '321 Elm St', 'Unit 3A', 'Tacoma', '98402'),
    ('William', 'Martinez', '[email protected]', '509-555-0105', '654 Maple St', NULL, 'Spokane', '99201'),
    ('Elizabeth', 'Taylor', '[email protected]', '206-555-0106', '987 Cedar St', NULL, 'Seattle', '98102'),
    ('David', 'Anderson', '[email protected]', '360-555-0107', '147 Pineview Dr', NULL, 'Bellevue', '98005'),
    ('Nancy', 'Thomas', '[email protected]', '425-555-0108', '369 Willow Ln', 'Apt 2C', 'Redmond', '98053'),
    ('Samantha', 'Jackson', '[email protected]', '253-555-0109', '852 Spruce St', NULL, 'Tacoma', '98403'),
    ('Daniel', 'White', '[email protected]', '509-555-0110', '681 Oakwood Ave', NULL, 'Spokane', '99202'),
    ('Sophia', 'Harris', '[email protected]', '206-555-0111', '408 Park Pl', 'Unit 1A', 'Seattle', '98103'),
    ('Christopher', 'Clark', '[email protected]', '360-555-0112', '246 Evergreen Dr', NULL, 'Bellevue', '98006'),
    ('Olivia', 'Lewis', '[email protected]', '425-555-0113', '533 Pinecrest Ct', NULL, 'Redmond', '98054'),
    ('Joshua', 'Young', '[email protected]', '253-555-0114', '129 Elmwood St', 'Apt 3B', 'Tacoma', '98404'),
    ('Mia', 'Walker', '[email protected]', '509-555-0115', '926 Maplewood Rd', NULL, 'Spokane', '99203'),
    ('Matthew', 'Hall', '[email protected]', '206-555-0116', '734 Cedarwood Dr', NULL, 'Seattle', '98104'),
    ('Madison', 'Allen', '[email protected]', '360-555-0117', '349 Pineview Ln', 'Apt 5C', 'Bellevue', '98007');

Stored procedure for selecting a list of customers

The following script creates a store procedure that will return all the customers in the customer table.

sp_GetAllCustomers.sql

CREATE PROCEDURE sp_GetAllCustomers
AS
BEGIN
    SELECT
        id,
        first_name,
        last_name,
        email,
        phone_number,
        address_line1,
        address_line2,
        city,
        state,
        zip_code,
        country
    FROM
        customers;
END;

Stored procedure for selecting one customer

The following script will create a store procedure to return a single customer record for the provided customer id.

sp_GetCustomerById.sql

CREATE PROCEDURE sp_GetCustomerById
    @CustomerId UNIQUEIDENTIFIER
AS
BEGIN
    SELECT
        id,
        first_name,
        last_name,
        email,
        phone_number,
        address_line1,
        address_line2,
        city,
        state,
        zip_code,
        country
    FROM
        customers
    WHERE
        id = @CustomerId;
END;

Stored procedure for creating or updating a customer record

The following script creates a stored procedure that will add a new customer record if the customer id does not exist. It will update an existing customer if the id does exist.

sp_InsertOrUpdateCustomer.sql

CREATE PROCEDURE sp_InsertOrUpdateCustomer
    @CustomerId UNIQUEIDENTIFIER = NULL,
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @Email NVARCHAR(100),
    @PhoneNumber NVARCHAR(15),
    @AddressLine1 NVARCHAR(100),
    @AddressLine2 NVARCHAR(100) = NULL,
    @City NVARCHAR(50),
    @ZipCode CHAR(5),
    @State CHAR(2) = 'WA',
    @Country NVARCHAR(50) = 'USA'
AS
BEGIN
    IF EXISTS (SELECT 1 FROM customers WHERE id = @CustomerId)
    BEGIN
        UPDATE customers
        SET
            first_name = @FirstName,
            last_name = @LastName,
            email = @Email,
            phone_number = @PhoneNumber,
            address_line1 = @AddressLine1,
            address_line2 = @AddressLine2,
            city = @City,
            state = @State,
            zip_code = @ZipCode,
            country = @Country
        WHERE
            id = @CustomerId;
    END
    ELSE
    BEGIN
        INSERT INTO customers (
            id,
            first_name,
            last_name,
            email,
            phone_number,
            address_line1,
            address_line2,
            city,
            state,
            zip_code,
            country
        )
        VALUES (
            @CustomerId,
            @FirstName,
            @LastName,
            @Email,
            @PhoneNumber,
            @AddressLine1,
            @AddressLine2,
            @City,
            @State,
            @ZipCode,
            @Country
        );
    END;
END;