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

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Table-Valued Store Procedure Parameters - Day 25 of 35 joes2pros4 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.

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

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.

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

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.

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

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

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.

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

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.

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

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)

,
Previous Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Easy Introduction to CHECK Options – Day 24 of 35
Next Post
SQL SERVER – Author’s Book is Available in India and USA

Related Posts

73 Comments. Leave new

  • Option 4 is the correct answer to accept a table-vauled parameter as opposed to a SQL data type parameter.

    Country: United States

    Reply
  • Hi Pinal,

    Challenge:
    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

    Correct Answer:
    The correct choices are #4 and #5.

    Explanation:
    The request was to create a stored procedure which accepts a table-valued parameter named @Suppliers. There was no requirement restricting which Table Type to utilize. Both choices #4 and #5 fit that requirement.

    Choices #1, #2 and #3 are not using a table-valued parameter in their definitions.

    Country:
    United States

    Thanks for the knowledge!

    Regards,

    Bill Pepping

    Reply
  • Rajesh Mohanrangan
    August 25, 2011 6:30 pm

    Answer:Option 4

    4.CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY

    Regards
    Rajesh
    From India

    Reply
  • Correct answer is: 4 (I am assuming GeographyType is not the same as SupplierType and it should contains table table for Geography column)

    4. CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY

    First 3 options doesn’t have table valued parameter and as per the question, we need to create a stored procedure which accepts a table-valued parameter named @Suppliers.

    Option 5 contains GeographyType (I am assuming GeographyType is not the same as SupplierType).

    so, correct is option 4.

    Country: India

    Reply
  • Answer is #4

    CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY

    Per our mini lesson above the syntax should be
    CREATE PROCEDURE ProcedureName @TableVariableName TableSchema READONLY

    That throws out the first three options because they are using a single data type not a table and the last option the name of the table doesn’t really fit with the question so 4 is the better answer.

    Thanks for the lesson. I had heard about this feature and thought it would fix some inefficiencies here, but never looked into it at all because we need to be compatiable with 2005 here at work.

    USA

    Reply
  • P.Anish Shenoy
    August 25, 2011 6:53 pm

    Hi Sir,

    The correct answer is option no 4

    CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY

    P.Anish Shenoy,
    INDIA

    Reply
  • Mike Michalicek
    August 25, 2011 7:30 pm

    Options 4 and 5 seem to be the correct answer.
    Options 1,2,3 are excluded because of the datatype and we do not know the data types for SupplierType and GeographyType.

    USE

    Mike Michalicek

    Reply
  • Leonardo Guerrero
    August 25, 2011 8:00 pm

    I think the correct answer in this case is option 4

    CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY

    because the question request table-valued parameter not other type

    Leonardo Guerrero

    Country : Chile

    Reply
  • The correct option is #4

    CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY

    The first three options are already predefined data types. The last option 5 is using the GeographyType datatype, which we don’t know anything about.

    The most reasonable answer is Option 4 which uses the create procedure statement with the @Suppliers associated with the SupplierType user defined data type.

    Country of Residence: USA

    Reply
  • Diljeet kumari
    August 25, 2011 8:17 pm

    Hello Pinal,

    The Correct Option for the above Question 25) is Option 4.

    CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY

    Explanation:

    Given :
    We need to create a stored procedure which accepts a table-valued parameter named @Suppliers. What code will achieve this result because we need SupplierType as the type.

    SQL Server lets you create custom data types that are based on system data types. Create a user-defined data type when you specify the same limitations often. For instance, if many tables contain a state column, base a user-defined data type on SQL Server’s nchar (see #1) with a length of 2 and name it State. Then, choose State as the column’s data type, instead of specifying nchar(2). It requires about as much work, but it’s self-documenting and easy to remember. This example is simple; usually a user-defined data type is a bit more complex.

    @suppliers is added as table type variable and Readonly keyword must be specified when passing a table datatype to any stored procedure other then this all are having a data type specified.

    Diljeet Kumari
    Country : India

    Reply
  • If we first ran the code:
    CREATE TYPE SupplierType AS TABLE ()

    Then the correct answer would be 4. CREATE PROCEDURE AddSuppliers @Suppliers SupplierType READONLY

    Matt Nelson, USA

    Reply
  • Answer: Option 4
    The other options are incorrect as they are all standard data types. We have to create a new user defined table type to use in our SP.

    Country: USA

    Reply
  • Correct Option is 4

    I’m from INDIA

    Reply
  • 4.CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY

    Gordon Kane
    Allen TX
    USA

    Reply
  • Option 4

    David
    USA

    Reply
  • Kalyanasundaram.K
    August 25, 2011 11:32 pm

    Correct Answer : Option 4

    4) CREATE PROCEDURE AddSuppliers
    Suppliers SupplierType READONLY

    Chennai, TamilNadu, India

    Reply
  • dilipkumarjena
    August 25, 2011 11:36 pm

    the correct option for the above question is option 4)

    CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY

    dilip kumar jena
    country : india

    Reply
  • Sale Ahmadu A.
    August 26, 2011 12:10 am

    The correct answer is #4
    CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY.

    (Sale, Nigeria)

    Reply
  • Partha Dutta Gupta
    August 26, 2011 12:38 am

    The Correct answer is :-

    4) CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY

    Since float, int , money and geographyType are not table type , so option 4 is the correct answer.

    Reply
  • Option : 4

    From USA

    Reply

Leave a Reply

Menu