I am going to talk about Transaction SQL variables. Any stored program that does complex business processing will use one of more of variables to craft an algorithm.
During my college days, I had to take a couple physics classes. Newton’s second law states that force is equal to mass times acceleration.
The comic strip on the left shows how variables in the fourth picture can be used as place holders in a complex equation. This concept is key for crafting very complex programs.
The DECLARE keyword is used in Transaction SQL (T-SQL) to define a variable. Unlike some languages, all variables in T-SQL need to be defined before they are used.
The example below declares a variable, stores text in the variable and shows the value of the variable.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- -- My first program -- -- Declare variable DECLARE @VAR_A VARCHAR(128); -- Set variable SET @VAR_A = 'Hello World'; -- Show the variable PRINT @VAR_A; PRINT ''; |
The output of this program is listed below.
|
1 2 3 4 |
output: Hello World </span> |
The first way to store a value in a variable is to use the SET keyword as shown above.
The PRINT keyword is used to display either a constant text string or a text variable. The output of this statement is directed to the results or message window depending upon the output mode of the query in the SQL Server Management Studio (SSMS).
In short, this statement is a quick way to add debugging lines to your code during development.
A second way to store a value in a variable is to use the SELECT keyword. The example below redesigns the code above using this keyword.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
/* My second program */ /* Declare variable */ DECLARE @VAR_B VARCHAR(128); /* Another way to set */ SELECT @VAR_B = 'Neo, the matrix has you!'; /* Show the variable */ PRINT @VAR_B; PRINT ''; |
The output of this program is listed below.
|
1 2 3 |
output: Neo, the matrix has you! |
Please note that the first program is using a multi-line comment (/* */) while the second program uses a single-line comment (—).
The above two programs are very simplistic in nature. A more realistic piece of code is to list all the databases. System databases to be exact. This program shows how the DECLARE statement can be used to initialize a variable on the same line.
The EXEC keyword executes the dynamic SQL in another session and returns the results to SSMS.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- -- My Third Program -- -- Declare & set DECLARE @VAR_SQL VARCHAR(128) = 'select convert(varchar(10), name) as db_name, database_id as db_id from sys.databases where database_id < 5'; /* Run the statement */ EXEC (@VAR_SQL); |
The output of this program is listed below.
|
1 2 3 4 5 6 7 8 |
output: db_name db_id ---------- ----------- master 1 tempdb 2 model 3 msdb 4 |
To recap this talk, variables are a great way to store results between statements in a program. Use the DECLARE keyword to define and optionally set your variable. Both the SET and SELECT keywords can be used to assign a value to a variable.
Last but not least, please comment your program. The next TSQL developer will thank you!
So is the moral of the story…use variables for complex database programming, and to be sure to comment your code……..OR…..physicists always lose snowball fights!!!
Nice Job John!!!