Composition way in T-SQL programming

These components allow handling of various operations that cannot be completed in a single statement. Users can group several commands together and use one of the following ways:

• Batches : A list of one or more commands sent as a query unit from the application to the server
• Stored Procedures : A stored procedure is a collection of precompiled and predefined T-SQL commands on the server.
• Triggers : a trigger is a type of stored procedure that is executed when the user processes an event such as INSERT,UPDATE, DELETE on the table.
• Scripts : A series of T-SQL statements stored in a file and used for input via SSMS or sqlcmd
• Variables : a variable that can be used by the user as an input of a T-SQL statement
• Control-of-flow : control dideefu flow for creating conditional constructs in T-SQL
• Error Handling: error handling mechanism used to control errors and provide information to users when errors are encountered.

T-SQL Batches

The T-SQl is a group of one or more statements sent as a query unit from the application to the server. SQL Server compiles the batch into a single unit of execution, called an execution plan. In the execution plan, SQL will execute the statements one by one. Each statement ends with a “;”. This condition is optional, but the basis for ending the statement without the “;” is deprecated and may be removed in the new version of SQL Server. So use dauas ; to end the statement is always recommended.

When a compile error such as a syntax error is encountered, the batch will not be executed.

A run-time error such as a constraint violation or algorithmic logic will result in one of the following situations occurring:

• Most run-time errors will stop the current command and the following statements in the batch
• A run-time error specified as a constraint will be used only if the statement exists and the remaining parts of the statement are still executed.

SQL statements executed before the runtime-error occurs are not affected. The only exception is when the batch is a transaction and the error occurs in the transaction when there is a rollback.

For example, suppose that there are 10 statements in the batch and 6 statements have an error, then the remaining statements in the batch will not be executed. If the batch is compiled and the third command fails, the first two statements will be executed.

Some rules to follow when using batches:

• CREATE FUNCTION, CREATE DEFAULT, CREATE RULE, CREATE TRIGGER, CREATE PROCEDURE, CREATE VIEW or CREATE SCHEMA cannot be concatenated with another statement in the batch. The CREATE statement starts the batch and all remaining statements inside the batch are considered part of the CREATE statement definition.
• no changes were made in the table and the new columns referenced back to the way they were
• If the first statement in the batch is an execute statement, then the excute command is not required, it is only required if the execute statement does not exist in the first statement in the batch.

Batch example:

USE AdventureWorks2019; GO GREATE VIEW dbo.vProduct AS SELECT ProductNumber, Name FROM Production.Product; GO SELECT * FROM dbo.vProduct; GO

Example 2 is used to combine multiple BATCHs in a transaction:

BEGIN TRANSACTION GO USE AdventureWorks2019; GO CREATE TABLE Company( Id_Num int IDENTITY(1,1), Company_Name nvarchar(100)) GO INSERT Company (Company_Names) ('Company 1') INSERT Company (Company_Names) ('Company 2') INSERT Company (Company_Names) ('Company 3') GO SELECT Id_Num, Company_Name FROM dbo.Company ORDER BY Company_Name ASC; GO COMMIT; GO

In the above code, several batches are combined into one transaction. The BEGIN TRANSACTION and COMMIT statements define the opening and closing of the transaction. CREATE TABLE, BEGIN TRANSACTION, SELECT, COMMIT and USE are single statement batches. INSERT statements are all packaged in one batch.

Variable (variable) in T-SQL

In SQL Server, variables can be declared and used as parameters or write dynamic queries, as a parameter passed in from the application. SQL Server provides statements to declare and assign values to variables.

DECLARE

The variable is initialized via the DECLARE statement in the batch body. These variables are assigned values using SELECT or SET statements. Variables are initialized with the value NULL if no value is provided by the user at the time of declaration.

Syntax:

DECLARE {(@local_variable [AS] data_type) } [= value]}

For example:

USE AdventureWorks2019; GO DECLARE @find varchar(30) = 'Man%'; SELECT p.LastName, p.FirstName, ph.PhoneNumber FROM Person.Person AS p JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID WHERE LastName LIKE @find;

SET

The SET statement assigns a value to a local variable initialized with DECLARE to determine the value of the variable

Syntax:

SET {@local_variable = {expression}} {@local_variable {+= | -= | *= | /= | %= | &= | ^= | |=}}

Operator explanation:

• += add and assign equals
• -= subtract and assign equals
• *= multiply and assign equals
• /= divide and assign equals
• %= divide by remainder and assign equal to
• &= bitwise AND and assign afterwards
• ^= bitwise XOR and assign afterwards
• |= Bitwise OR and assign afterwards.

For example:

DECLARE @myvar char(20); SET @myvar = 'Hello world';

SELECT

The SELECT statement specifies the local variable specified through the DECLARE statement to retrieve the expression

Syntax:

SELECT {@local_variable {= | += | -= | *= | /= | %= | &= | ^= | != } expresssion}

For example:

USE AdventureWorks2019 GO DECLARE @var1 nvarchar(30); SELECT @var1 = 'company hello'; SELECT @var1 = Name FROM Sales.Store WHERE BusinessEntityID = 10; SELECT @var1 AS 'Company name';

The SET and SELECT statements look similar, but have a slight difference:

• Only one variable can be assigned at a time using SET, however, SELECT can assign values to multiple variables at the same time.
• SET can only assign one basic data type assignment from the query, will error when the data is a set of rows and columns. However, SELECT can assign multiple return values to a variable.

Synonyms

Synonyms is a database object that serves the following purposes:

• It suggests a different name for different database objects, also called base object, which can exist on remote or local server.
• It represents an abstraction layer that protects the client application from modifications made to the location and name of the base object.

For example, consider the Department table in AdventureWorks2019 which is located on a server named server1. To refer to a table from server 2 named server2, the client will have to use four element names:
server1.AdventureWorks2019.HumanResources.Department

If the location of the table is modified, for example to another server, the client application needs to be unaffected by this change. To solve both of these problems, users can create a synonym DeptEmpTable owr Server2 for the Department table on Server1
From there, the client only needs to use one named DeptEmpTable to refer to the Department table.

Similarly, if the location of the Department table changes, the user needs to edit the synonym , DeptEmpTable to point to the new location of the Department table.
And note, without the ALTER Synonym statement, you need to drop the synonym and re-initialize it with the old name but pointing to the new location of the Department .

List of database objects for which the user can create synonyms:

• Extended stored procedures
• SQL Table-valued function
• SQL stored procedures
• Table (user-defined)
• Replication-filter-procedure
• SQL scalar functions
• SQL inline-tabled-valued function
• View

Synonyms and schemas

Suppose a user wants to create a synonym and has a default schema that is not owned by them. In this case, they may be able to identify the synonym name with the scema they actually own. For example, user owns schema Resources but Material is default schema, if user want to create synonym, they must prefix synonym with schema Resources .

Assign permissions on Synonyms

Only members of the db_owner or db_ddladmin roles or the synonym owner are allowed to grant permissions to a synonym. A user can deny, grant or revoke all or any permissions on a synonym.

List of permissions:

• DELETE
• INSERT
• TAKE OWNERSHIP
• VIEW DEFINITION
• CONTROL
• EXECUTE
• SELECT
• UPDATE

Working with Synonyms

Users work with synonyms in SEL SERver 2019 or T-SQL in SSMS. To create a synonym in SSMS using object explorer do the following:

To create a synonym using T-SQL, use the syntax:

CREATE SYNONYM [schema_name_1.] synonym_name FOR <object> <object> ::= { server_name.[database_name].[schema_name_2].|database_name.[schema_name_2].|schema_name_2.] object_name}

For example:

USE AdventureWorks2019; GO CREATE SYNONYM MyAddressType FOR AdventureWorks2019.Person.AddressType; GO

Program Flow Statements

The different types of program flow statements supported by T-SQL are as follows:

T-SQL Control-of-Flow language

Control-of-flow shows the flow of SQL statements, blocks of commands, user-defined functions, and stored procedures.

By default, T-SQL statements are executed sequentially. Program flow allows statements to be executed in specific, interrelated situations and is generated between code using constructs similar to programming languages.

List of Control-Of-Flow Language keywords:

• RETURN
• THROW
• TRY…CATCH
• WAIT FOR
• WHILE
• BEGIN…END
• BREAK
• TIẾP TỤC
• GOTO label
• IF…ELSE

BEGIN…END

Syntax:

BEGIN { sql_statement | statement_block } END

For example

USE AdventureWorks2019; GO BEGIN TRANSACTION; GO IF @@TRANCOUNT = 0 BEGIN SELECT FristName, MiddleName FROM Person.Person WHERE LastName = 'Andy'; ROLLBACK TRANSACTION; PRINT N'Rolling back the transaction two times would cause an error.'; END; ROLLBACK TRANSACTION; PRINT N'Rolled back the transcaction'; GO

IF…ELSE

TRansact-SQL relies on the IF keyword and the condition executes only if it is satisfied and returns true. The ELSE keyword is optional in the T-SQl statement that is executed when the IF condition is not satisfied and returns FALSE.

Syntax:

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

For example:

USE AdventureWorks2019; GO DECLARE @ListPrice money; SET @ListPrice = (SELECT MAX(p.ListPrice) FROM Production.Product AS p JOIN Production.ProductSubcategory AS s ON p.ProductSubcategoryID = s.ProductSubcategoryID WHERE s.[Name] = 'Mountain Bikes'); PRINT @ListPrice IF @ListPrice < 3000 PRINT 'All the products in this category can be purchased for an amount less than 3000' ELSE PRINT 'The prices for some products in this category exceed 3000'

WHILE

The WHILE statement defines the condition for repeating block execution. The statement is executed repeatedly until the specified condition returns true. The execution statement in the WHILE loop can be controlled with the BREAK or CONTINUE . keyword

Syntax:

WHILE Boolean_expression {sql_statement | statement_block | BREAK | CONTINUE}

For example:

DECLARE @flag int SET @flag = 10 WHILE (@flag <= 95) BEGIN IF @flag % 2 = 0 PRINT @flag SET @flag = @flag+ 1 CONTINUE; END; GO

CASE WHEN

Syntax:

 SELECT column_list, CASE column_name WHEN value1 then display1 WHEN value2 then display2 ... ELSE display END [AS alias] FROM relative_table [WHERE condition];

For example

 SELECT studentid, studentname, GENDER = CASE gender WHEN 1 then 'Male' WHEN 0 then 'Female' ELSE 'Unknow' END FROM Student;