Structured Query Language 2152022 Pierce College CIS 261

Structured Query Language 2/15/2022 Pierce College CIS 261 SQL 1

Module 8: Variables 2/15/2022 Pierce College CIS 261 SQL 2

Variables “Variables allow you to temporarily store data values for later use in the same batch in which they were declared. A batch is one T-SQL statement or more sent to SQL Server for execution as a single unit. ” Microsoft SQL Server T-SQL Fundamentals, page 339 2/15/2022 Pierce College CIS 261 SQL 3

Variable Types • Any native data type can be used by a variable • Table data types can also can be set as variables • Common Table Expressions (CTE’s) can be used as variables. • Variables are scoped as LOCAL to the batch being run. • Declare variables with the DECLARE statement • Set variable values with either the SET or SELECT statements Example: DECLARE @myvar char(20); SET @myvar = 'This is a test'; SELECT @myvar; GO Returns: “This is a test”; https: //msdn. microsoft. com/en-us/library/ms 189484(v=sql. 110). aspx 2/15/2022 Pierce College CIS 261 SQL 4

Variable Declaration “Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values by using either a SET or SELECT statement. Cursor variables can be declared with this statement and used with other cursor-related statements. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration. ” https: //msdn. microsoft. com/en-us/library/ms 188927. aspx DECLARE @find varchar(30); SET @find = 'Man%'; SELECT p. Last. Name, p. First. Name, ph. Phone. Number FROM Person AS p JOIN Person. Phone AS ph ON p. Business. Entity. ID = ph. Business. Entity. ID WHERE Last. Name LIKE @find; 2/15/2022 Pierce College CIS 261 SQL 5

Variable Declaration - continued Variables can be declared in groups, separated by commas DECLARE @Group nvarchar(50), @Sales money; SET @Group = N'North America'; SET @Sales = 2000000; SELECT First. Name, Last. Name, Sales. YTD FROM Sales. v. Sales. Person WHERE Territory. Group = @Group AND Sales. YTD >= @Sales; 2/15/2022 Pierce College CIS 261 SQL 6

Variable Declaration- Table Variables Table variables can be declared to represent temporary tables. They can be operated on like tables. DECLARE @Categories table( [Category. ID] [int] IDENTITY(1, 1) NOT NULL, [Category. Name] [nvarchar](15) NOT NULL, [Description] [ntext] NULL, [Picture] [image] NULL) INSERT INTO @Categories(Category. Name, Description, Picture) VALUES('New Category', 'Very Cool Category', NULL) SELECT * FROM @Categories 2/15/2022 Pierce College CIS 261 SQL 7

Variable Declaration – CTE’s A common table expression (CTE) “is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. A common table expression can include references to itself. This is referred to as a recursive common table expression. ” https: //msdn. microsoft. com/en-us/library/ms 175972. aspx ; WITH CTE(Category. ID , Category. Name , Description, Picture) AS (SELECT * FROM Categories WHERE Category. Name LIKE 'C%') SELECT * FROM CTE 2/15/2022 Pierce College CIS 261 SQL 8

Using Common Table Expressions A Common Table Expression is a reference to the table, not a copy. So UPDATING, INSERTING and DELETING to the CTE is doing the same to the source table. UPDATE example ; WITH CTE(Category. ID, Category. Name, Description, Picture) AS (SELECT * FROM Categories WHERE Category. Name LIKE 'C%‘ ) UPDATE CTE SET Description = CONCAT(Description, '. I was appended via the CTE') SELECT * FROM Categories 2/15/2022 Pierce College CIS 261 SQL 9

Using Common Table Expressions INSERTING a record via a CTE ; WITH CTE(Category. ID, Category. Name, Description, Picture) AS (SELECT * FROM Categories) INSERT INTO CTE(Category. Name, Description, Picture) VALUES('Inserted', 'Inserted Category Description', NULL) SELECT * FROM Categories 2/15/2022 Pierce College CIS 261 SQL 10

Using Common Table Expressions DELETING a record via a CTE ; WITH CTE(Category. ID, Category. Name, Description, Picture) AS (SELECT * FROM Categories) DELETE CTE WHERE Category. ID = 10 SELECT * FROM Categories 2/15/2022 Pierce College CIS 261 SQL 11

Using Common Table Expressions Using CTE’s to get results with a JOIN --Declare the CTE's ; WITH CTE 1 AS (SELECT * FROM Categories a), CTE 2 AS (SELECT * FROM Products p) --Use the CTE's in a JOIN to get a result SELECT CTE 1. Category. Name, CTE 2. Product. Name FROM CTE 1 INNER JOIN CTE 2 ON CTE 1. Category. ID = CTE 2. Category. ID 2/15/2022 Pierce College CIS 261 SQL 12

Combine CTE’s and other variables Example of using an integer variable and a CTE to limit the results via a WHERE clause Declare the variable for the WHERE clause, followed by semicolon. Then declare CTE ; --Start with semicolon for CTE DECLARE @Cat. ID int = 8; WITH CTE AS (SELECT * FROM Categories WHERE Category. ID = @Cat. ID ) SELECT * FROM CTE 2/15/2022 Pierce College CIS 261 SQL 13

This work is licensed under a Creative Commons Attribution 4. 0 International License Led by Bellevue College, the Health e. Workforce Consortium was formed to elevate Health Information Technology workforce development locally and nationally and provide career paths into this promising field for veterans and others. The ninecollege consortium includes Bellevue College, Bellingham Technical College, Clark College, Clover Park Technical College, Northern Virginia Community College, Pierce College, Renton Technical College, Spokane Community College, and Whatcom Community College. The Health Information and Management Systems Society (HIMSS) is also a primary partner. This workforce solution was 100% funded by an $11. 7 m grant awarded by the U. S. Department of Labor's Employment and Training Administration, Grant #TC-23745 -12 -60 -A-53. The solution was created by the grantee and does not necessarily reflect the official position of the U. S. Department of Labor. The Department of Labor makes no guarantees, warranties, or assurances of any kind, express or implied, with respect to such information, including any information on linked sites and including, but not limited to, accuracy of the information or its completeness, timeliness, usefulness, adequacy, continued availability or ownership. 2/15/2022 Pierce College CIS 261 SQL 14