/ ---
/ jupyter:
/   jupytext:
/     text_representation:
/       extension: .q
/       format_name: light
/       format_version: '1.5'
/       jupytext_version: 1.14.4
/   kernelspec:
/     display_name: SQL
/     language: sql
/     name: SQL
/ ---

/ + [markdown] azdata_cell_guid="27d8be3b-8e80-4e07-b09e-47dd877029a0"
/ # Variables

/ + azdata_cell_guid="ec25df8c-d7d0-426c-839f-62aa57e1928a"
-- Search Customer by city using variable (scoped to batch)
DECLARE @City varchar(20) = 'Toronto';
SET @City = 'Bellevue'

SELECT c.CustomerID, c.FirstName + ' ' + c.LastName AS CustomerName, a.City
FROM SalesLT.Customer AS c JOIN SalesLT.CustomerAddress AS ca ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a ON ca.AddressID = a.AddressID
WHERE a.City = @City
-- Note: Do not put GO command between variable declaration and query using that variable (cuz they're sent as different batches for execution)

/ + azdata_cell_guid="8fda8c8e-1708-44f3-bc02-3b225779b2cd"
-- Assign value to variable using SELECT query and output it
DECLARE @MaxListPrice int;
SELECT @MaxListPrice = MAX(ListPrice)
FROM SalesLT.Product

PRINT 'Maximum List Price in Product table: ' + CAST(@MaxListPrice AS varchar(20));

/ + [markdown] azdata_cell_guid="0e121626-d6f0-4512-90be-bcc2052a262c"
/ # Conditional Branching

/ + azdata_cell_guid="c52ded71-0671-469b-b586-b9ab936d8aac"
IF 'Yes' = 'yes'
    PRINT 'True'
ELSE
    PRINT 'False'

/ + azdata_cell_guid="008edada-1659-4ec6-80ba-546ecbc20feb"
-- Display a custom message to the user if UPDATE statement affected any rows
DECLARE @pid int = 680;

UPDATE SalesLT.Product
SET DiscontinuedDate = GETDATE()
WHERE ProductID = @pid;

DECLARE @RowsAffected int = @@ROWCOUNT;

IF @RowsAffected < 1
    PRINT 'No product found.'
ELSE
    BEGIN
        PRINT CAST(@RowsAffected AS varchar(10)) + ' product updated.'
        SELECT * FROM SalesLT.Product WHERE ProductID = @pid;
    END

-- Note: @@ROWCOUNT is a system variable which returns the number of rows affected by the previous query

/ + [markdown] azdata_cell_guid="a6d729ca-d6ef-43a5-8d87-87be496115f0"
/ # Looping

/ + azdata_cell_guid="810d7748-1b11-42ce-9c60-6996ba65f2f7" tags=[]
DECLARE @DemoTable AS TABLE (Description varchar(20));
DECLARE @Counter int = 1;

WHILE @Counter <= 5
    BEGIN
        INSERT INTO @DemoTable
        VALUES ('Row ' + CAST(@Counter AS varchar(20)));
        SET @Counter += 1;
    END

SELECT * FROM @DemoTable;

/ + [markdown] azdata_cell_guid="4d99f9b9-2d42-4392-9292-356e06e1c5ae"
/ # Stored Procedures

/ + azdata_cell_guid="cf43fd3c-f258-4dbf-afbb-c3cbf2a991f2"
-- Create a stored procedure to get all Products by ProductCategoryID
CREATE PROCEDURE SalesLT.GetProductsByCategoryID (@CategoryID INT = NULL)
AS
IF @CategoryID IS NULL
    SELECT ProductID, Name, Color, Size, ListPrice
    FROM SalesLT.Product;
ELSE
    SELECT ProductID, Name, Color, Size, ListPrice
    FROM SalesLT.Product
    WHERE ProductCategoryID = @CategoryID;
GO  -- Marks the end of procedure definition batch

-- Execute procedure without parameters
EXEC SalesLT.GetProductsByCategoryID;
-- Execute procedure with parameter
EXEC SalesLT.GetProductsByCategoryID 6;

StarcoderdataJupyterScriptsDedupFiltered