In this article, we will learn how to passing data table as parameter to stored procedures for SQL servers with different examples.
A stored procedure is a bunch of statements that stored in the database. stored procedures accept the parameters and execute the T-SQL statements in procedure and get the result returns.
Read also: SQL Between
Stored procedures are easy to edit or modify code inside and the stored procedure without needed to restart the application. And easily eliminate the storing the code anytime in the database. and needed to change the logic in procedure just use Alter procedure statement.
Passing Data table as Parameter to Stored Procedures for SQL Server are the name of over the network that different of writing T-SQL Queries and Reduced the network traffic.
stored procedures are reusable and can be executed the multiple users without code again.
The applications need to make multiple requests from the real-world database and numbers are huge. each request requires time to executes.
The stored procedure are provide the performance in SQL server easy to use and when the execute for the first time and that store the value. It can be provide when it executes next time.
Explanation:
There is a requirement to create a table valued parameter is a parameter with a table type column and a data type in a database and creating the same data using multiple times in anywhere called user Defined data types (UDDT).
Table type parameter using you can send multiple rows of data to a stored procedure in SQL command in form of a table.
In some times user-defined table types provide flexibility and better performance and temporary table in some cases. I am using READONLY arguments that operation in a table-valued parameter that are cannot perform the data manipulation like as insert, delete, insert in the stored procedure.
Create a database:
create a stored procedure with parameter using below syntax:
create database school;
use school;
In the above code, we create a database called school and create a table and named it class .The class table has 3 columns,
- student_id,
- student_name,
- student_class
create table class
(
student_id int primary key,
student_name varchar(50) not null ,
student_class varchar(50) not null,
);
Create a user-defined table type:
Create type schoolTableType As Table
(
student_id int primary key,
student_name varchar(50) not null,
student_class varchar(50) not null
);
Now ,we want to create UDTT "schoolTableType" .This is the variable to pass the parameter using stored procedure. And the column of the "schoolTableType" variable are similar to the class table.
And Now create a stored procedure that parameter accepts the variable for insert the data into class table.
Create a store procedure with parameters
CREATE PROCEDURE spInsertclass
@schoolType schoolTableType Readonly
AS
BEGIN
Insert into class
Select * from @schoolType
END
In the stored procedure created as spInsertclass that parameter name should be accepted by schoolTableType and assign the value on @schoolType.
Create a stored procedure with output parameters:
Declare @schoolTableType schoolTableType
Insert into @schoolTableType values (1,'ramesh','VII')
Insert into @schoolTableType values (2,'suersh','X')
Insert into @schoolTableType values (3,'chandra','VI')
Insert into @schoolTableType values (4,'priya','VI')
Insert into @schoolTableType values (5,'swetha','IX')
Insert into @schoolTableType values (6,'ali','X')
Insert into @schoolTableType values (7,'christian','X')
execute spInsertclass @schoolTableType
In above code insert some records into spInsertcars and the stored procedure pass the value on @schoolTableType. Execute the spInsertclass and the @schoolTableType variable are inserted into the class table.
Select * from class;
Now, select all the from class table and the result will show the insert record.
Result :
I hope you will learn how to Passing Data table as Parameter to Stored Procedures for SQL Server and Just share your friend and family . copy the code and recreate enjoy...