SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Table-Valued Store Procedure Parameters – Day 25 of 35

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?

1. CREATE PROCEDURE AddSuppliers
   @Suppliers Float READONLY
2. CREATE PROCEDURE AddSuppliers
   @Suppliers Int READONLY
3. CREATE PROCEDURE AddSuppliers
   @Suppliers Money READONLY
4. CREATE PROCEDURE AddSuppliers
   @Suppliers SupplierType READONLY
5. 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)