SQL user-defined table types, using data types as parameters in stored procedures

The use of data types in database programming can improve the reusability of code. They are often used in methods and stored procedures. Using the data type, we can avoid defining a bunch of parameters in the stored procedure, which is the equivalent of the object-oriented language. An object is passed into a method, and the object has a variety of properties, and the stored procedure simply acquires the object to obtain the individual parameters and then handles it accordingly. The difference is that the table type of SQL is capable of including more than one piece of data. What is the matter, and look at the following examples.

1. First I created a student table, including four fields, and the primary key is a self-growing type starting from 1.

Gocreate TABLE STUDENT (ID INT IDENTITY (PRIMARY key,name varchar), SEX char,phone varchar (20),);

2. Define the table type Sutdenttype, including three fields, corresponding to the name,sex and phone of the student table respectively. I was prepared to insert the new student data into the stored procedure. Take it for a number of references.

Gocreate TYPE Sutdenttype as TABLE (NAME varchar, SEX char,phone varchar (20));

3. Create a stored procedure that inserts student information. Its parameter type is the table type Studenttype.

Remove each student record stored in the table type and insert it into the Student information table.

Gocreate PROCEDURE addstudent@new_student as Sutdenttype Readonlyasinsert into STUDENT (name, sex, PHONE) SELECT name, sex, PHONE from @NEW_STUDENT; RETURN 0;

4. Finally establish the parameters and run the stored procedure addstudent.

Godeclare @NEW_STUDENT as Sutdenttypeinsert @NEW_STUDENT VALUES (' YLD ', ' M ', ' 888 ')      , (' MM ', ' F ', ' 123 ')      , (' HQG '), ' F ', ' 678 '); EXEC addstudent @NEW_STUDENT

5. Query the information of the student table and find that three student information has been successfully inserted.

SQL user-defined table types, using data types as parameters in stored procedures