While working with bigger and complex query, we might need to hold some data temporarily to use it or perform some query on. In these scenario, we can use Temporary table variable. The name of the temporary table variable starts with "@" as against temporary table whose name starts with #.
DECLARE @InactiveRecords AS TABLE ( PersonalDetailId int, FullName varchar(50) ) INSERT INTO @InactiveRecords SELECT PersonalDetailsId, FirstName + ' ' + LastName FROM PersonalDetails WHERE Active = 0 SELECT IR.FullName, ac.Salary, ac.PPFDeduction, IR.PersonalDetailId FROM @InactiveRecords as IR, Accounts ac WHERE IR.PersonalDetailId = ac.PersonalDetailsId
In the above code snippet, we have declared a temporary table variable named “@InactiveRecords” with PersonalDetailsId and FullName column. Next, we are filling this table with inactive data from PersonalDetails table and then using this temp table to join with Accounts and getting other details and listing it.
It is mandatory to use alias with the temporary table variable while referencing in the SELECT query.
Remember that as this is a table variable, so the name must be prefixed with ‘@’ character. The scope of this variable exists only within session or stored procedure where it is declared.Views: 6165 | Post Order: 110