T-SQL BASIC

DECLARE @local_variable data_type [,@local_variable data_type,…]

DECLARE @CustomerID CHAR(5)
DECLARE @CustomerID CHAR(5), @CustomerName VARCHAR(30)

SET @local_variable = value

-- הצהרה של משתנה והשמת ערך
DECLARE @CustomerID CHAR(5) 
SET @CustomerID = 'AROUT'

-- שימוש בערך של המשתנה
SELECT CompanyName, ContactName, Phone 
FROM Customers 
WHERE CustomerID = @CustomerID 

-- מבנה
DECLARE @Local_Variable_1 <Data_Type>, @Local_Variable_2 <Data_Type>,
SET @Local_Variable_1 = (SELECT <Column_1> from <Table_Name> where <Condition_1>)

-- דוגמא
DECLARE @COURSE_NAME VARCHAR (10)
SET @COURSE_NAME = (select Tutorial_name from Guru99 where Tutorial_ID = 3)
PRINT @COURSE_NAME

-- מבנה
DECLARE @LOCAL_VARIABLE <Data_Type>
SELECT @LOCAL_VARIABLE = <Value>

--דוגמא
DECLARE @COURSE_ID INT
SELECT @COURSE_ID = 5

-- מבנה לכמה ערכים
DECLARE @Local_Variable _1 <Data_Type>, @Local_Variable _2 <Data_Type>,
SELECT @Local_Variable _1 = <Value_1>,  @Local_Variable _2 = <Value_2>

-- דוגמא. יכול לעשות השמה לכמה ערכים במקביל
DECLARE @COURSE_ID as INT, @COURSE_NAME AS VARCHAR(5)
SELECT @COURSE_ID = 5, @COURSE_NAME = 'UNIX'

-- מבנה השמה מתוך שאילתא. השאילתא חייבת להחזיר שורה אחת ועמודה אחת, כלומר ערך ספציפי.
DECLARE @Local_Variable_1 <Data_Type>, @Local_Variable _2 <Data_Type>,
SELECT @Local_Variable _1 = (SELECT <Column_1> from <Table_name> where <Condition_1>)

-- דוגמא
DECLARE @COURSE_NAME VARCHAR (10)
SELECT @COURSE_NAME = (select Tutorial_name from Guru99 where Tutorial_ID = 1)

PRINT @local_variable

SELECT @local_variable

IF Boolean_expression 
    {sql_statement | statement_block} 
[ELSE 
    {sql_statement | statement_block}]

-- בלוק ליותר מפקודה אחת
BEGIN
    {sql_statement, statement_block}
END

DECLARE @x INT 
SET @x = 1 

IF (1 = @x)
    BEGIN 
        PRINT 'You are number one! '
    END 
ELSE
    BEGIN 
        PRINT 'Loser ;-( '
    END

IF EXISTS (SELECT * 
        FROM Orders 
        WHERE CustomerID = 'VINET') 
    BEGIN
        PRINT 'The Customer has Order'
    END 
ELSE 
    BEGIN 
        DELETE Customers 
        WHERE CustomerID = 'VINET' 
        PRINT 'Customer deleted' 
    END

DECLARE 
    @x INT, 
    @Case_result VARCHAR(20) 
SET @x =1 
SET @Case_Result = CASE @x 
                    WHEN 1 THEN 'You are number one!' 
                    WHEN 2 THEN 'Almost …' 
                    ELSE 'Loser :-(' 
                    END 
PRINT @Case_Result

-- מבנה
WHILE Boolean_expression 
    {sql_statement | statement_block}

-- מבנה
DECLARE @n INT 
SET @n = 1 

WHILE (SELECT COUNT(*) FROM Employees) >= @n 
    BEGIN 
        IF (SELECT LastName FROM Employees 
            WHERE EmployeeID = @n) = 'King' 
            BEGIN 
                PRINT 'King EmployeeID =' + CAST(@n AS VARCHAR(2)) 
                BREAK 
            END 
        ELSE 
            SET @n = @n + 1 
    END

-- מבנה
Label:
    Sql_statments | statement block
IF condition GOTO Label

DECLARE @emp_id NUMERIC(4), 
        @name VARCHAR(30), 
        @b_date DATETIME 
SET @emp_id = 4 

LABEL: 
    SELECT @name =lastname, @b_date = birthdate 
    FROM employees
     WHERE employeeid = @emp_id 
     PRINT @name + CAST(@b_date AS VARCHAR) 
     SET @emp_id = @emp_id +1 

IF @name LIKE '%a%' GOTO LABEL 
PRINT CHAR(10)+'That''s how GOTO LOOP works...'

-- מבנה
BEGIN TRY
    Sql statements
END TRY
BEGIN CATCH
    Sql statements
END CATCH

-- מבנה
RAISERROR (message_string | message_id | variable ,severity, state [,])

EXEC sp_addmessage 
    @msgnum = value over 50000, 
    @severity = value between 1 and 25, 
    @msgtext = ‘The message’

-- ex1
DECLARE @FirstVar CHAR(30), @SecondVar VARCHAR(30)

SET @FirstVar = 'MY FIRST'
SET @SecondVar = 'VARIABLE'

PRINT @FirstVar+' '+@SecondVar

-- ex2
DECLARE @StrPrompt CHAR(30), @TodayDate Date

SET @StrPrompt = 'THE DATE TODAY IS: '
SET @TodayDate = getdate()

PRINT @StrPrompt+cast(@TodayDate as char)

-- ex3
DECLARE @Emp4Name CHAR(30)
SELECT @Emp4Name = (select lastName from Employees where EmployeeID = 4)

PRINT @Emp4Name

-- ex4
DECLARE @Prod5Name CHAR(30), @Prod5Cat CHAR(30)
SELECT @Prod5Name = (select ProductName from Products where ProductID = 5)
SELECT @Prod5Cat = (select c.CategoryName from Products p 
    inner join Categories c on p.CategoryID = c.CategoryID where p.ProductID = 5)

PRINT @Prod5Name+@Prod5Cat

-- ex5
DECLARE @Prod9Price INT

SELECT @Prod9Price = (select unitprice from Products where ProductID = 17)

IF (@Prod9Price > 50)
    BEGIN 
        PRINT 'I LIKE THIS PRODUCT '
    END 
ELSE
    BEGIN 
        PRINT 'I DO NOT LIKE THIS PRODUCT '
    END

select * from Products

-- ex6
DECLARE @ProdName CHAR(50), @ProdPrice INT

SELECT @ProdName = 'Tarte au sucre'
SELECT @ProdPrice = (select unitprice from Products where ProductName = @ProdName)

IF (@ProdPrice < 20)
    BEGIN 
        set @ProdPrice = @ProdPrice * 1.1
        PRINT 'New 10% price '+cast(@ProdPrice as char)
    END 
else IF (@ProdPrice >= 20 and @ProdPrice < 40)
    BEGIN 
        set @ProdPrice = @ProdPrice * 1.2
        PRINT 'New 20% price '+cast(@ProdPrice as char)
    END 
ELSE
    BEGIN 
        set @ProdPrice = @ProdPrice * 1.5
        PRINT 'New 50% price '+cast(@ProdPrice as char)
    END

-- ex7
DECLARE @n INT, @EmpLastName CHAR(20)
SET @n = 1 

WHILE (SELECT COUNT(*) FROM Employees) >= @n 
    BEGIN 
        select @EmpLastName = (SELECT LastName FROM Employees WHERE EmployeeID = @n)

        IF @EmpLastName like '%e%' 
            BEGIN 
                PRINT cast(@n as char)+' - Letter e in last name = ' + @EmpLastName 
                SET @n = @n + 1 
            END 
        ELSE 
            SET @n = @n + 1 
    END

-- ex8
DECLARE @cat_name VARCHAR(30),
        @cat_num INT

SET @cat_num = 4

LABEL: 
    SELECT *
    FROM categories
    WHERE Categoryid = @cat_num

    SELECT @cat_name = categoryname
    FROM categories
    WHERE categoryid = @cat_num
    
    SET @cat_num = @cat_num + 1

IF @cat_name LIKE '%a%'
    GOTO LABEL
ELSE
    SELECT 'NO MORE CATEGORIES WITH THE LETTER A!'



-- מעבר בלולאה על כל הרשומות שיש לי בטבלה
-- פעולה יקרה, אם אין צורך לא משתמשים בה
DECLARE 
    @product_name VARCHAR(250), 
    @list_price  DECIMAL;

DECLARE cursor_product CURSOR
FOR SELECT 
        product_name, 
        list_price
    FROM 
        production.products; --50

OPEN cursor_product;

FETCH NEXT FROM cursor_product INTO 
    @product_name, 
    @list_price;

WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @product_name + CAST(@list_price AS varchar);
        FETCH NEXT FROM cursor_product INTO 
            @product_name, 
            @list_price;
    END;

CLOSE cursor_product;

DEALLOCATE cursor_product;