sql table type code example

Example 1: create user defined table type in sql

CREATE TYPE UT_Employee AS TABLE  
(  
Emp_Id int NOT NULL,  
EmployeeName nvarchar(MAX),  
EmpSalary varchar(50),  
StateId varchar(50),  
CityId varchar(50)  
);
CREATE PROCEDURE USP_Insert_Employee_Infi(@Employee_Details [UT_Employee])  
AS  
BEGIN  
  
INSERT INTO dbo.Employee  
(  
Emp_Id,  
EmployeeName,  
EmpSalary,  
StateId,  
CityId  
)  
SELECT * FROM @Employee_Details  
END;

Example 2: sql types

1- Data Manipulation Language(DML)-(SELECT, INSTERT, UPDATE...)
DML statements affect records in table. These are
basic operations we perform on data such as selecting
few records from a table, inserting new records,
deleting unnecessary records, updating existing records.

2-Data Definition Language (CREATE , ALTER , DROP)...
DDL statements are used to modify database or
table schema. These statements handle the design
and storage of database objects.

3-Data Control Language (GRANT , REVOKE)...
DCL statements control the level of access that
users have on database objects

4- Transaction Control Language (BEGIN TRAN, COMMIT TRAN, ROLLBACK)...

TCL statements allows us to control and manage
transactions to maintain the integrity of data
withing SQL statements.