Answer simple quiz at the end of the blog post and –
Every day one winner from India will get Joes 2 Pros Volume 4.
Every day one winner from United States will get Joes 2 Pros Volume 4.
Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLProgrammingChapter5.1Setup.sql script from Volume 4.
Table-Valued Store Procedure Parameters
Stored procedures can easily take a single parameter and use a variable to populate it. A stored procedure can readily handle two parameters in this same fashion. However, passing 1000 variables into a stored procedure would be unwieldy and would require the calling code to run 1000 times. SQL Server 2008 now offers a way to simply pass a table into a parameterized stored procedure. That’s right – you can pass a table’s worth of data into a single parameter and accomplish all the needed processing with just one call.
Table Types
We are already familiar with data types like int, varchar, and money. We can also create our own user-defined types. With the new “table” data type available in SQL Server 2008, we can create a user-defined data type that is based upon a table.
Our first step in preparing our table-valued parameter demonstration is to create a “table” data type. We need to consider the fields to be included in the table which we want our stored procedure to accept, as well as the data types of these fields. Perhaps your table will look just like the Employee table. Perhaps the table this stored procedure will use is like no other table on your system. In the latter case, you don’t have to create a new persistent table: you can define a table design without creating a table.
Using Table Types as Variables
After creating a new table type, our next step will be to declare a variable whose data type will be our new table type. In our previous examples, once we declare a variable, we can set it equal to a value or pass in a value. In the case of a table type, the value of that variable will be a result set.
Parameters will allow you to pass in any data type found in the database, including user-defined types. When you can create and declare a user-defined table type and pass that into a stored procedure, this is known as a table-valued parameter.
The first table type we will define we will call GrantTableType and it will be based upon two fields of the Grant table (GrantName and Amount). The code to accomplish this is shown in the Figure below.
After you run this code and create this new type, locate your newely created GrantTableType in Object Explorer. Traverse to JProCo > Programmability > Types > User-Defined Table Type > dbo.GrantTableType. The GrantTableType can be seen in your Object Explorer.
Now let’s declare a variable (@GrantTVP) whose data type is GrantTableType (i.e., our newly created table type). After we declare the variable, we will insert some data into it. Looking at the SELECT statement, we know this will bring in two fields and eleven records from the Grant table.
DECLARE @GrantTVP AS GrantTableType
INSERT INTO @GrantTVP
SELECT GrantName, Amount
FROM [Grant]
The confirmation message tells us that our @GrantTVP variable has been populated with 11 rows. We get an error message if we attempt to query from @GrantTVP unless we declare our variable, fill it with data, and select from it all at once as shown seen in the figure below.
Table Types as Parameters
So what’s the advantage of using a table type? To answer that question, let’s first take a look at some familiar tables and their limitations. The MgmtTraining table, contains the approved list of classes for JProCo’s managers. The MgmtTrainingNew table contains the list of classes we intend to approve soon. Currently there are only two fields and two records in the MgmtTrainingNew table. The MgmtTrainingNew table has two fields.
Table-Valued Parameters
Once a class from the MgmtTrainingNew table is approved, that class record must be placed in the MgmtTraining table. Now let’s think about how we would add these two records using a stored procedure. Would we run the stored procedure twice (i.e., once for each record)? A better choice would be to pass the entire MgmtTrainingNew table into a stored procedure and have that stored procedure populate the MgmtTraining table.
We’re going to pass in a value to our parameter @TableName and then use that parameter in the logic of our stored procedure. Let’s add a statement to create a new data type (MgmtTrainingType). Notice that we must add the type to the code of our sproc. When passing in a table type, you must set it to READONLY.
CREATE TYPE MgmtTrainingType AS TABLE
(ClassName VARCHAR(50) NOT NULL,
ClassDurationHours INT NULL);
CREATE PROCEDURE AddNewTraining @TableName MgmtTrainingType READONLY
AS
INSERT INTO dbo.MgmtTraining
(ClassName, ClassDurationHours,ApprovedDate)
SELECT mt.ClassName,mt.ClassDurationHours,GETDATE()
FROM @TableName AS mt
GO
We will declare a variable named @ClassTVP using the table type (MgmtTrainingType) we created earlier. This table-type variable (@ClassTVP) is then populated with records from the MgmtTrainingNew table. We then can call upon the stored procedure AddNewTraining and pass this variable into the table-valued parameter.
Let’s run a query on the MgmtTraining table and check to see whether the new class records appear. You can see in the Figure below was have success! Both of the new records now show up in the MgmtTraining table.
Question 25
You need to create a stored procedure which accepts a table-valued parameter named @Suppliers. What code will achieve this result?
- CREATE PROCEDURE AddSuppliers
@Suppliers Float READONLY - CREATE PROCEDURE AddSuppliers
@Suppliers Int READONLY - CREATE PROCEDURE AddSuppliers
@Suppliers Money READONLY - CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY - CREATE PROCEDURE AddSuppliers
@Suppliers GeographyType READONLY
Rules:
Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Every day one winner from India will get Joes 2 Pros Volume 4.
Every day one winner from United States will get Joes 2 Pros Volume 4.
The contest is open till next blog post shows up at which is next day GTM+2.5.
Reference: Pinal Dave (https://blog.sqlauthority.com)
73 Comments. Leave new
Option # 4 should be the answer
CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY
Ritesh (India)
Correcto answer is No. 4, because we are creating a procedure which accepts a table-valued parameter named @Suppliers, and that parameter might be a SupplierType datatype.
Rene Castro
El Salvador
Hi,
Option 4 is the correct answer.
The reason is that it must of type SupplierType DataType.
Option 5 can also be true as there is no pre-defined data type as GeographyType. But the best answer looks to be 4.
Thanks
Sudhir Chawla
New Delhi, India
Option 4 is the correct answer.
4.CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY
All other options have a datatype is specified. @suppliers is actually defined as table type variable. Readonly should be specified when passing a table datatype.
Thank you
Uday Bhoopalam
USA
The correct answer is #4
CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY
Number #5 can potentially be also a correct answer if for some unknown reason we modeled GeographyType to be actually based on the Suppliers table.
CREATE PROCEDURE AddSuppliers
@Suppliers GeographyType READONLY
However, based on the name I believe the #4 is correct.
I am from USA
Answer is option 4 because all others are having different datatypes other than suppliertype
Sathya
Chennai, India
I would chose 4)
4) CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY
4) is correct because we need a table type and not any other type as in 1, 2 and 3
Syntactically 5) is also correct if i chose to name my table type as GeographyType. So I guess the question is a little ambiguous.
Leo Pius
USA
Store Procedure:
In DBMS, a stored procedure is a set of SQL statements with an assigned name that’s stored in the database in compiled form so that it can be shared by a number of programs. The use of stored procedures can be helpful in controlling access to data, preserving data integrity, and improving productivity.
User-Defined Data Types:
In SQL Server 2008, a user-defined table type is a user-defined type that represents the definition of a table structure which can be used in declare table-valued parameters for stored procedures or functions. Following is an Eg of creating a user-defined datat type which can support the explanation more better and precise,
CREATE TYPE IdentityDataType As Table
(
FirstName nvarchar(64) Not Null,
MiddleName nvarchar(64) NULL,
LastName nvarchar(64) Not Null,
Age int NOT NULL
)
Now the stored procedure can take the ‘ IdentityDataType’ type as input parameter. Inside the stored procedure, the parameter is available as a temporary table. This table however has to be readonly.
Eg is as follows,
CREATE PROCEDURE PersonIdentity
@Suppliers IdentityDataType READONLY
So, I go with OPTION # 4
ie,
CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY
Store Procedure:
In DBMS, a stored procedure is a set of SQL statements with an assigned name that’s stored in the database in compiled form so that it can be shared by a number of programs. The use of stored procedures can be helpful in controlling access to data, preserving data integrity, and improving productivity.
User-Defined Data Types:
In SQL Server 2008, a user-defined table type is a user-defined type that represents the definition of a table structure which can be used in declare table-valued parameters for stored procedures or functions. Following is an Eg of creating a user-defined datat type which can support the explanation more better and precise,
CREATE TYPE IdentityDataType As Table
(
FirstName nvarchar(64) Not Null,
MiddleName nvarchar(64) NULL,
LastName nvarchar(64) Not Null,
Age int NOT NULL
)
Now the stored procedure can take the ‘ IdentityDataType’ type as input parameter. Inside the stored procedure, the parameter is available as a temporary table. This table however has to be readonly.
Eg is as follows,
CREATE PROCEDURE PersonIdentity
@Suppliers IdentityDataType READONLY
So, I go with OPTION # 4
ie,
CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY
COUNTRY : INDIA
Answer is option 4
CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY
Expalanation:
Create procedure readonly
India
Option 4 is True.
Azhar Iqbal
Country Pakistan.
Correct Answer is # 4.
CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY
When you can create and declare a user-defined table type and pass that into a stored procedure, this is known as a table-valued parameter.
When passing in a table type, you must set it to READONLY.
Gopalakrishnan Arthanarisamy
Unisys, Bangalore, India.
Hi,
Correct ans is
4. CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY
Remaining all are Sql Define Data Type.
I am from India.
Regards,
Kaushik Modi
ans 4:
CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY
In your option all variable are systemdefineType except SupplierType( Which is user Define Type) .If it is created by inheriting Table type and it is populated with value then it will accept table-valued parameter.
Partha,
India
Other variables are float,int,money, geography type which can not accept table value parameter.
option 4 is correct ans.
Hi,
The Answer is option 4,i.e.
4. CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY
Reason:
With the new “table” data type available in SQL Server 2008, we can create a user-defined data type that is based upon a table.
When you can create and declare a user-defined table type and pass that into a stored procedure, this is known as a table-valued parameter. Which as per the question is “suppliertype” since, int,float,money refers to a datatype for a variable which can hold a single record at a time. Geographytype is a spatial datatype used in CLR stored procedures via .NET.Hence, option 4 is correct.
Regards,
Geetika Bhambri
Ahmedabad(Gujarat)-INDIA
The correct answer of this question is option 4
That is
CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY
Here “AddSuppliers” is procedure name , “@Suppliers” is table value parameter and “SupplierType” is custom table type.
Mahmad Khoja
INDIA
Correct Answer is
4. CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY
City: Baroda
Country: India
Thanks
GurjitSingh
Coorect Answer Option 4 and 5
CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY
CREATE PROCEDURE AddSuppliers
@Suppliers GeographyType READONLY
Float,money,int are datatypes in sql server that can’t use with table type parameter.
We will misunderstand GeographyType is datatype but Geography is the datatype in sql server.
So if SupplierType or GeographyType are table type then 4 and 5 option are correct.
Nikhildas
Cochin
INDIA
Hi,
As description given above “dd a statement to create a new data type (MgmtTrainingType). Notice that we must add the type to the code of our sproc. When passing in a table type, you must set it to READONLY.”
It’s clear that ##Option 4 is the right Answer.
“CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY”
Thanks….
Rajneesh Verma
(INDIA)
Correct Answer is option:4
As per explanation in article following code is use:
CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY
Country:India
Thanks,
Fazal Vahora