https://www.w3schools.com/sql/

https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-ver16#interleaved-execution-for-mstvfs

https://learn.microsoft.com/en-us/training/modules/create-stored-procedures-table-valued-functions/4-procedure

Create View

CREATE VIEW SalesLT.vProductsRoadBikes AS
SELECT ProductID, Name, ListPrice
FROM SalesLT.Product
WHERE ProductCategoryID = 6;

Use a derived table

SELECT DerivedTable.ProductID, DerivedTable.Name, DerivedTable.ListPrice
FROM
    (
        SELECT
        ProductID, Name, ListPrice,
        CASE WHEN ListPrice > 1000 THEN N'High' ELSE N'Normal' END AS PriceType
        FROM SalesLT.Product
    ) AS DerivedTable
WHERE DerivedTable.PriceType = N'High';

SELECT

FilmLength

,COUNT(*) AS NumberOfFilms

FROM

(

SELECT

CASE

WHEN FilmRunTimeMinutes < 100 THEN 'Short'

WHEN FilmRunTimeMinutes < 150 THEN 'Medium'

WHEN FilmRunTimeMinutes < 200 THEN 'Long'

ELSE 'Epic'

END AS FilmLength

FROM

tblFilm

) AS FilmLengths

GROUP BY

FilmLength

Stored Procedure With Multiple Parameters

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;
EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';

Working with batches

  • Batches are boundaries for variable scope, which means a variable defined in one batch may only be referenced by other code in the same batch
  • Some statements, typically data definition statements such as CREATE VIEW, CREATE FUNCTION, and CREATE PROCEDURE may not be combined with others in the same batch.
  • When a runtime error occurs on one line, the next line may be executed, unless you’ve added error handling to the code.
INSERT INTO dbo.t1 VALUES(1,2,N'abc');
INSERT INTO dbo.t1 VALUES(2,3,N'def');
GO

Declare and assign variables and synonyms

DECLARE @var1 AS INT = 99;
DECLARE @var2 AS NVARCHAR(255);
SET @var2 = N'string';
DECLARE @var3 AS NVARCHAR(20);
SELECT @var3 = lastname FROM HR.Employees WHERE empid=1;
SELECT @var1 AS var1, @var2 AS var2, @var3 AS var3;
GO

Working with synonyms

To manage synonyms, use the data definition language (DDL) commands CREATE SYNONYM, ALTER SYNONYM, and DROP SYNONYM

CREATE SYNONYM dbo.ProdsByCategory FOR TSQL.Production.ProdsByCategory; GO EXEC dbo.ProdsByCategory @numrows = 3, @catid = 2;

Use conditional logic in your programs using IF…ELSE

IF OBJECT_ID('HR.Employees') IS NULL --this object does exist in the sample database
BEGIN
    PRINT 'The specified object does not exist';
END
ELSE
BEGIN
    PRINT 'The specified object exists';
END;
IF EXISTS (SELECT * FROM Sales.EmpOrders WHERE empid =5)
BEGIN
    PRINT 'Employee has associated orders';
END;

looping using WHILE statements

DECLARE @empid AS INT = 1, @lname AS NVARCHAR(20);
WHILE @empid <=5
   BEGIN
	SELECT @lname = lastname FROM HR.Employees
		WHERE empid = @empid;
	PRINT @lname;
	SET @empid += 1;
   END;

Use variables with batches

DECLARE 
@empname nvarchar(30),
@empid int;

SET @empid = 5;

SET @empname = (SELECT FirstName + N' ' + LastName FROM SalesLT.Customer WHERE CustomerID = @empid)

SELECT @empname AS employee;

SET CASE

DECLARE 
@i int = 8,
@result nvarchar(20);

SET @result = 
CASE 
WHEN @i < 5 THEN
    N'Less than 5'
WHEN @i <= 10 THEN
    N'Between 5 and 10'
WHEN @i > 10 THEN
    N'More than 10'
ELSE
    N'Unknown'
END;

SELECT @result AS result;

Execute loops with WHILE statements

DECLARE @i int = 1; WHILE @i <= 10 BEGIN PRINT @i; SET @i = @i + 1; END;

DECLARE 
@salesOrderNUmber nvarchar(30),
@customerID int;

SET @customerID = 29847;

SET @salesOrderNUmber = (SELECT salesOrderNumber FROM SalesLT.SalesOrderHeader WHERE CustomerID = @customerID)

SELECT @salesOrderNUmber as OrderNumber;

Select the customer first name and last name and display:

DECLARE @customerID AS INT = 1;
DECLARE @fname AS NVARCHAR(20);
DECLARE @lname AS NVARCHAR(30);

WHILE @customerID <=10
BEGIN
    SELECT @fname = FirstName, @lname = LastName FROM SalesLT.Customer
        WHERE CustomerID = @CustomerID;
    PRINT @fname + N' ' + @lname;
    SET @customerID += 1;
END;

Execute a stored procedure by a user

EXEC dbo.uspGetEmployeeManagers

If the stored procedure is the first statement in the T-SQL batch, the procedure can be executed without the EXECUTE or EXEC keyword.

To check the exact system procedure names, use the catalog views:

sys.system_objects

sys.system_parameters

Use the sp_procoption to run a stored procedure every time an instance of SQL Server is started. The syntax is:

EXEC sp_procoption @ProcName = myProcedure , @OptionName = ‘startup’ , @OptionValue = ‘on’;