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 http://blog.sqlauthority.com which is next day GTM+2.5.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

About these ads

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

  1. 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

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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

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

  8. Answer is option 4
    CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY

    Expalanation:
    Create procedure readonly

    India

  9. 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.

  10. 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.

  11. 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

  12. 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

  13. Correct Answer is

    4. CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY

    City: Baroda
    Country: India

    Thanks
    GurjitSingh

  14. 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

  15. 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)

  16. 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

  17. Hi,

    Expalanation seems to be not pasted completely in the comment posted before.

    Answer is option 4.
    Explanation:
    procedurename followed by parametername followed by tablevariablename followed by readonly” modifier

    Razeena

    Country:India

  18. Correct Answer : 4

    Reason :
    1) we have created a user defined table type named suppliertype which actually having same parameter defination as for supplier table.
    2)with the help of this we can pass a user-defined table type to store procedure “AddSupplier”.
    3)Advantage of doing this is that we don’t need to call Add Supplier stored procedure multiple times to insert records in Supplier Table.

  19. In my opinion both options 4 and 5 are correct.

    In option 1,2 and 3 data type of variable is either float,int or money which are reserved data types of MS-SQL and will not accept table-valued parameters.
    In case of option 4 and 5, we can create user defined table type as

    CREATE TYPE SupplierType AS TABLE
    (Columnname1 datatype NOT NULL,
    Columnname2 datatype NULL,…);

    OR

    CREATE TYPE GeographyType AS TABLE
    (Columnname1 datatype NOT NULL,
    Columnname2 datatype NULL,…);

    then option 4 and 5 both can accept table valued parameter @Suppliers.
    Hence, Both Option 4 and 5 are correct

    Country – INDIA (Gujarat)

  20. Here, we have to pass Table-Valued parameter. Hence the options 1,2,3 & 5 are eliminated as they can just pass on Float, Int, Money and Geography respectively.

    We have to create our own user-defined data type that is based upon a ‘table’ type.

    For the above question, we create “SupplierType” data type.
    Then Create a Procedure having Parameter of our custom “SupplierType” data type.

    Thus,
    4. CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY

    is the correct answer.

    Ishan Shah,
    Gandhinagar,
    India

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

    Thank you Pinal Sir for the examples. I had read about this, but the examples provide better understanding. Thank you once again for this series.

    Sudeepta,
    India.

  22. I think there is kind of a typing error in the Options for today’s question.

    “Geography” is the spatial data type implemented as a .NET common language runtime (CLR) data type in SQL Server but “GeographyType” is not an implemented data type.

    Hence it is possible to answer with option 5 also.
    By means of creating a user defined data type named “GeographyType”.

  23. The Correct Answer is:

    4.CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY

    Explanation:

    @Suppliers is the table type parameter name of the table type SupplierType.
    [SupplierType is the table type]
    When passing in a table type, we must set it to READONLY. Hence READONLY option.

    Country:

    India

  24. ANS : 4

    CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY

    in Option 1,2,3 parameter is not a table-valued parameter

    option 5 can also a correct answer if we modeled GeographyType based on the Suppliers table

    CREATE PROCEDURE AddSuppliers
    @Suppliers GeographyType READONLY

    However, as the name suggest I believe the 4 is correct.

    Thanks,
    Mitesh Modi
    (India)

  25. Hi,

    The correct option is : 4

    CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY

    where a @Suppliers is the variable whose data type is SupplierType (the created table type) .

    Name: Hema Chaudhry
    Location: India

    Thanks
    Hema Chaudhry

  26. Answer:
    I think the correct answers are options 4 and 5. Both have a table variable type as input parameter.

    This procedure accepts a table variable of type SupplierType as input parameter. Assuming that Table variable SupplierType is created as per the requirement (with columns and appropriate data type).We can narrow down to option 4 as the name indicates.

    Raj
    USA

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

    Country: United States

  28. 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

  29. 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

  30. 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

  31. Hi Sir,

    The correct answer is option no 4

    CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY

    P.Anish Shenoy,
    INDIA

  32. 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

  33. 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

  34. 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

  35. 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

  36. 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

  37. Correct Answer : Option 4

    4) CREATE PROCEDURE AddSuppliers
    Suppliers SupplierType READONLY

    Chennai, TamilNadu, India

  38. 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.

  39. 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

  40. 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

  41. 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

  42. 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

  43. Correct Option is (4)

    CREATE PROCEDURE AddSuppliers @Suppliers SupplierType READONLY

    — G.Venkatesh Prabu || Bangalore || India

  44. Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

  45. 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.

  46. Pingback: SQL SERVER – Programming and Development – Book Available for SQL Server Certification Journey to SQLAuthority

  47. 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

  48. Pingback: SQL SERVER – Expanding Views – Contest Win Joes 2 Pros Combo (USD 198) – Day 4 of 5 « SQL Server Journey with SQL Authority

  49. Pingback: SQL SERVER – Weekly Series – Memory Lane – #044 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s