Working wth Variables in Database Interactions with Transact-SQL - Retrieving data from a database using a T-SQL script
(Page 2 of 4 )
Let us consider the following example:
use Northwind
go
declare @EmpID int
set @EmpID = 4
select LastName + ',' + FirstName
from dbo.Employees
where EmployeeID = @EmpID
go
In the above script, I am asking the script to work within the context of the "Northwind" database. If you are already connected to the same database, this may not be necessary. But it is always a good practice to include the "use" statement to switch to the respective database context you require.
The word "go," in simple terms, executes all the previous statement(s) immediately as a "batch." Going further down, we have the following:
declare @EmpID int
set @EmpID = 4
The above two statements simply declare and initialize a new variable named "@EmpID" (of type "int") with a value of 4. Every variable you use in the script must be declared prior to its usage and every variable must be preceded with the "@" symbol. Going further down, we have the following:
select LastName + ',' + FirstName
from dbo.Employees
where EmployeeID = @EmpID
The above SELECT statement simply retrieves the name (combined value of LastName and FirstName separated with comma) from the Employees table based on the EmployeeID available in the variable @EmpID.
Retrieving data from a database into variables using a T-SQL script
In the previous example, we directly used the SELECT statement to retrieve and display the information to the user. In general, when we are working with scripts, we may need to retrieve the values of the SELECT statement into variables and use them extensively as part of the entire script.
Let us consider the following example:
use Northwind
go
declare @ename varchar(20)
declare @EmpID int
set @EmpID = 4
select @ename = LastName + ',' + FirstName
from dbo.Employees
where EmployeeID = @EmpID
print 'Name of the Employee:' + @ename
go
In the above example, I declared one more variable, "@ename" of type string. You can also observe that it is not initialized directly as we did for "@EmpID." The following is the statement which does it.
select @ename = LastName + ',' + FirstName
from dbo.Employees
where EmployeeID = @EmpID
The above SELECT statement retrieves the name (combined LastName and FirstName) of the employee and assigns the value to the variable "@ename." When the SELECT statement assigns its values to the variables, it will not display any output to the user. So, we need another statement to display the value back to the user as follows:
print 'Name of the Employee:' + @ename
The above statement displays a message concatenated by the employee name retrieved. You can also work with more than one column as follows:
use Northwind
go
declare @Lname varchar(20)
declare @Fname varchar(20)
declare @EmpID int
set @EmpID = 4
select @Lname = LastName, @Fname = FirstName
from dbo.Employees
where EmployeeID = @EmpID
print 'Name of the Employee:' + @Lname + ',' + @Fname
go
Next: Common scenarios while retrieving data from a database into variables >>
More MS SQL Server Articles
More By Jagadish Chaterjee
