FAQ : Oracle %ROWTYPE equivalent in SQL Server

What is %ROWTYPE in Oracle?

The %ROWTYPE attribute provides a feature to create Table based or Cursor Based record. Ie. If you have a table with 5 column and you want to select all the five column value of a single record at a stretch instead of declaring 5 scalar variable you just declare a single variable of %ROWTYPE which will intern have all the five column. Consider we have a table called Department with three columns Department_ID, Description and Name. Either you can declare three scalar variable or you can declare single variable of %ROWTYPE attribute. If you want to store a collection (multiple records ) then you can use TABLE OF TableName%RowType;
See the below eg.
DECLARE
Dept_REC DEPARTMENT%ROWTYPE;
BEGIN
SELECT *
INTO Dept_REC
FROM DEPARTMENT
WHERE DEPARTMENT_ID = 1;
DBMS_OUTPUT.PUT_LINE (' DEPARTMENT ID: '|| Dept_REC.DEPARTMENT_ID);
DBMS_OUTPUT.PUT_LINE ('COURSE DESCRIPTION: '||
Dept_REC.DESCRIPTION);
DBMS_OUTPUT.PUT_LINE ('Name: '||
Dept_REC.Name);
END;

SQL Server Equivalent.

In SQL Server what can come close to TABLE OF TableName%RowType is Declare a table type variable (in case it is collection). Another way may be create a #temp table. But temp table method may involve IO which need to be aware of and also in there are limitation where you can create #temp table. For a single row, it is better to declare separate scalar variable for each columns

Table variable Method
Declare @Dept Table (Department_id Int,Name Varchar(100),Description Varchar(1000))
The below statement will insert all the rows from Department to @Dept variable.
Select *INTO @Dept
FROM
Department

Temp table method
Select *into #Temp_Dept
FROM Department
The above statement will create a temp table on the fly. This can be used in Procedure but not in Function.