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

  • Richa Aggarwal
    August 26, 2011 3:03 am

    Option 4 and 5 is the right answer.

    CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY

    CREATE PROCEDURE AddSuppliers
    @Suppliers GeographyType READONLY

    Explanation – Our requirement was to create a stored procedure that accepts a table valued parameter. Here, 1,2 and 3 options signify SQL defined datatypes(int, float and money).
    Options 4 and 5 are not SQL defined and can be user defined data types based on table data type.
    I assume SupplierType and GeographyType as user defined data types based on table and they might have definitions something like this.

    Create Type SupplierType as Table
    (supplierName varchar(20) not null, supplierID int not null)

    CreateType GeographyType as Table
    (supplierName varchar(20) not null, supplierID int not null, supplierCity varchar(10))

    Here, the name GeographyType should not be confused with geography spatial data type.
    The correct syntax for variables with geography data type is
    @declare someVariable geography.

    So, both SupplierType and GeographyType can be considered as table data types and hence, option 4 and option 5 both can be considered as the right answers.

    Name – Richa
    City – Seattle
    Country – USA

    Reply
  • Richa Aggarwal
    August 26, 2011 3:05 am

    Option 4 and 5 are the right answers.

    CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY

    CREATE PROCEDURE AddSuppliers
    @Suppliers GeographyType READONLY

    Explanation – Our requirement was to create a stored procedure that accepts a table valued parameter. Here, 1,2 and 3 options signify SQL defined datatypes(int, float and money).
    Options 4 and 5 are not SQL defined and can be user defined data types based on table data type.
    I assume SupplierType and GeographyType as user defined data types based on table and they might have definitions something like this.

    Create Type SupplierType as Table
    (supplierName varchar(20) not null, supplierID int not null)

    CreateType GeographyType as Table
    (supplierName varchar(20) not null, supplierID int not null, supplierCity varchar(10))

    Here, the name GeographyType should not be confused with geography spatial data type.
    The correct syntax for variables with geography data type is
    declare @someVariable geography

    So, both SupplierType and GeographyType can be considered as table data types and hence, option 4 and option 5 both can be considered as the right answers.

    Name – Richa
    City – Seattle
    Country – USA

    Reply
  • I believe correct answer is No. 4

    Shiv
    USA

    Reply
  • The correct answer is: option#) 4 and option #)5

    For support of my answer i collected some points , please refer, these are as follows:
    >>The basic explanation is that the parameter @Suppliers should be of Table type.

    >>Though option 4 and 5 both doesn’t contain table data type. Still , sql server provide us to create user defined type .

    >>I searched for the the SupplierType and GeographyType, I found that there are no keywords or datatype defined in sql server 2008 with name SupplierType and GeographyType. Though i found that sql server 2008 contains one data type as Geography but not GeographyType.

    >>The geography type is predefined and available in each database. You can create table columns of type geography and operate on geography data in the same manner as you would use other system-supplied types.

    >>Conclusion:>>> As SupplierType and GeographyType are not the keyword, Then it is possible to create user defined datatype , with names either SupplierType or GeographyType, which can contains table structure or we can say of table type.

    The explanation for other options are as follows.

    option 1) 1.CREATE PROCEDURE AddSuppliers
    @Suppliers Float READONLY

    this option uses Float datatype, which cant store someparameters value which contains table structure

    option 2) .CREATE PROCEDURE AddSuppliers
    @Suppliers Int READONLY

    this option uses Int datatype, which again can’t store some parameters value which contains table structure .

    3.CREATE PROCEDURE AddSuppliers
    @Suppliers Money READONLY

    this option uses Money datatype, which again can’t store some parameters value which contains table structure .

    Regards,
    Ragini Gupta
    India

    Reply
  • 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.

    Malay Shah,
    Ahmedabad, India

    Reply
  • Correct Option is (4)

    CREATE PROCEDURE AddSuppliers @Suppliers SupplierType READONLY

    — G.Venkatesh Prabu || Bangalore || India

    Reply
  • #4
    CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY

    Thanks,
    Neelesh
    London UK

    Reply
  • #4
    CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY

    Thanks,
    Neelesh

    London, UK

    Reply
  • A.) 4

    Winner from USA: Matt Nelson

    Winner from India: madhivanan

    I thank you all for participating here. The permanent record of this update is posted on facebook page.

    Reply
  • BTW, in my opinion answers that answer #4 only are not 100% correct. Answer #5 can also be an answer assuming someone named Suppliers table to be GeographyType (to confuse everyone). So, while #4 will be most likely correct we can not exclude #5 from the answers.

    So, I would re-run the winners for this question as just answer #4 is not enough, in my opinion.

    Reply
  • Option 4 is the correct answer

    Vaishali
    Country of Residence: hyderabad

    Reply
  • Shyamaprasad Sarkar
    April 13, 2012 11:10 am

    Hello Pinal

    I have a question. Can we update one column of a table valued parameter with some value extracted from the actual table and then return the same through the parameter from the procedure?

    Regards,
    Shyamaprasad

    Reply

Leave a Reply

Menu