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 – Easy Introduction to CHECK Options – Day 24 of 35

SQL SERVER – Author’s Book is Available in India and USA »

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

August 25, 2011 by pinaldave

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

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

About these ads

Posted in Joes 2 Pros, Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 76 Comments

76 Responses

on August 25, 2011 at 7:09 am | Reply Ritesh Shah

Option # 4 should be the answer

CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY

Ritesh (India)
on August 25, 2011 at 7:18 am | Reply Rene Alberto Castro Velasquez

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
on August 25, 2011 at 7:36 am | Reply Sudhir Chawla

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
on August 25, 2011 at 7:50 am | Reply Uday Bhoopalam

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
on August 25, 2011 at 8:01 am | Reply Naomi

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
on August 25, 2011 at 8:08 am | Reply Sathya

Answer is option 4 because all others are having different datatypes other than suppliertype

Sathya
Chennai, India
on August 25, 2011 at 8:17 am | Reply levpius

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
on August 25, 2011 at 9:30 am | Reply Monalisa Das

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
on August 25, 2011 at 9:30 am | Reply Monalisa Das

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
on August 25, 2011 at 9:42 am | Reply Razeena

Answer is option 4
CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY

Expalanation:
Create procedure readonly

India
on August 25, 2011 at 10:00 am | Reply Azhar Iqbal

Option 4 is True.
Azhar Iqbal
Country Pakistan.
on August 25, 2011 at 10:02 am | Reply Gopalakrishnan Arthanarisamy

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.
on August 25, 2011 at 10:21 am | Reply Kaushik Modi

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
on August 25, 2011 at 10:31 am | Reply Partha Pratim Dinda

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
- on August 25, 2011 at 10:39 am | Reply Partha Pratim Dinda
  
  Other variables are float,int,money, geography type which can not accept table value parameter.
  option 4 is correct ans.
on August 25, 2011 at 10:43 am | Reply Geetika Bhambri

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
on August 25, 2011 at 10:57 am | Reply Mahmad Khoja

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
on August 25, 2011 at 11:04 am | Reply GurjitSingh

Correct Answer is

4. CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY

City: Baroda
Country: India

Thanks
GurjitSingh
on August 25, 2011 at 11:26 am | Reply Nikhildas

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
on August 25, 2011 at 11:34 am | Reply Rajneesh Verma

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)
on August 25, 2011 at 11:42 am | Reply Fazal Vahora

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
on August 25, 2011 at 11:51 am | Reply Razeena

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
on August 25, 2011 at 12:05 pm | Reply arjunyadav89

option 4 is correct

CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY

Arjun
India.
on August 25, 2011 at 12:05 pm | Reply Ashish Kadam

Hi Pinal,

Option 4 is correct.

Ashish Kadam -Pune-India
on August 25, 2011 at 12:35 pm | Reply yangamuniprasad

The correct answer is option 4

CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY

Thanks,
Prasad Yangamuni
INDIA (PUNE)
on August 25, 2011 at 12:37 pm | Reply vishal patwardhan (@vishalpatwardha)

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.
on August 25, 2011 at 12:38 pm | Reply Dayanand Singh

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)
on August 25, 2011 at 12:42 pm | Reply Nakul Vachhrajani

The Correct Answer is #4:
CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY

Country of residence: India
on August 25, 2011 at 12:46 pm | Reply Narendra

The Correct Answer For this Question is Option – 4

Thanks,
Narendra(India).
on August 25, 2011 at 12:56 pm | Reply Ishan Shah

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
on August 25, 2011 at 12:59 pm | Reply sudeepta

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.
on August 25, 2011 at 1:05 pm | Reply Ishan Shah

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”.
on August 25, 2011 at 1:18 pm | Reply Manjula T

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
on August 25, 2011 at 2:03 pm | Reply sreeram

As per your explantion, Correctd answer #4

Sreeram
India
on August 25, 2011 at 2:12 pm | Reply Shatrughna Kumar

Correct answer is option #4.

4.CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY

New Delhi
India
on August 25, 2011 at 2:17 pm | Reply Mitesh Modi

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)
on August 25, 2011 at 4:10 pm | Reply Hema Chaudhry

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
on August 25, 2011 at 4:17 pm | Reply Raj

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
on August 25, 2011 at 4:53 pm | Reply madhivanan

Answer 4

4.CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY

Country : India
on August 25, 2011 at 5:26 pm | Reply Nikhil Mahajan

the correct option is Option 4 i.e.

CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY

India
on August 25, 2011 at 5:36 pm | Reply ghanshyam

option 4

ghanshyam
india
on August 25, 2011 at 6:24 pm | Reply Don

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

Country: United States
on August 25, 2011 at 6:25 pm | Reply Bill Pepping

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
on August 25, 2011 at 6:30 pm | Reply Rajesh Mohanrangan

Answer:Option 4

4.CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY

Regards
Rajesh
From India
on August 25, 2011 at 6:36 pm | Reply Hardik Doshi

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
on August 25, 2011 at 6:47 pm | Reply Deb

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
on August 25, 2011 at 6:53 pm | Reply P.Anish Shenoy

Hi Sir,

The correct answer is option no 4

CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY

P.Anish Shenoy,
INDIA
on August 25, 2011 at 7:30 pm | Reply Mike Michalicek

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
on August 25, 2011 at 8:00 pm | Reply Leonardo Guerrero

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
on August 25, 2011 at 8:06 pm | Reply David Seefeld

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
on August 25, 2011 at 8:17 pm | Reply Diljeet kumari

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
on August 25, 2011 at 9:18 pm | Reply Matt Nelson

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
on August 25, 2011 at 9:45 pm | Reply Ryan J. Adams

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
on August 25, 2011 at 10:09 pm | Reply sravan

Correct Option is 4

I’m from INDIA
on August 25, 2011 at 10:41 pm | Reply Gordon Kane

4.CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY

Gordon Kane
Allen TX
USA
on August 25, 2011 at 11:04 pm | Reply David Brust

Option 4

David
USA
on August 25, 2011 at 11:32 pm | Reply Kalyanasundaram.K

Correct Answer : Option 4

4) CREATE PROCEDURE AddSuppliers
Suppliers SupplierType READONLY

Chennai, TamilNadu, India
on August 25, 2011 at 11:36 pm | Reply dilipkumarjena

the correct option for the above question is option 4)

CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY

dilip kumar jena
country : india
on August 26, 2011 at 12:10 am | Reply Sale Ahmadu A.

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

(Sale, Nigeria)
on August 26, 2011 at 12:38 am | Reply Partha Dutta Gupta

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.
on August 26, 2011 at 2:19 am | Reply raghavendra

Option : 4

From USA
on August 26, 2011 at 3:03 am | Reply Richa Aggarwal

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
on August 26, 2011 at 3:05 am | Reply Richa Aggarwal

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
on August 26, 2011 at 4:36 am | Reply Shiv Rampal

I believe correct answer is No. 4

Shiv
USA
on August 26, 2011 at 10:49 am | Reply ragini gupta

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
on August 26, 2011 at 12:33 pm | Reply Malay Shah

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
on August 26, 2011 at 3:16 pm | Reply gvprabu

Correct Option is (4)

CREATE PROCEDURE AddSuppliers @Suppliers SupplierType READONLY

– G.Venkatesh Prabu || Bangalore || India
on August 26, 2011 at 3:54 pm | Reply SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

[...] Q 25) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Table-Valued Store Procedure Par… [...]
on August 26, 2011 at 4:13 pm | Reply Neelesh

#4
CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY

Thanks,
Neelesh
London UK
on August 26, 2011 at 4:14 pm | Reply Neelesh

#4
CREATE PROCEDURE AddSuppliers
@Suppliers SupplierType READONLY

Thanks,
Neelesh

London, UK
on August 26, 2011 at 7:05 pm | Reply pinaldave

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.
on August 29, 2011 at 8:25 am | Reply Naomi

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.
on August 30, 2011 at 4:52 pm | Reply vaishali jain

Option 4 is the correct answer

Vaishali
Country of Residence: hyderabad
on September 5, 2011 at 7:01 am | Reply SQL SERVER – Programming and Development – Book Available for SQL Server Certification Journey to SQLAuthority

[...] Q 25) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Table-Valued Store Procedure Par… [...]
on April 13, 2012 at 11:10 am | Reply Shyamaprasad Sarkar

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
on August 9, 2012 at 7:01 am | Reply SQL SERVER – Expanding Views – Contest Win Joes 2 Pros Combo (USD 198) – Day 4 of 5 « SQL Server Journey with SQL Authority

[...] SQL Joes 2 Pros Development Series – Table-Valued Store Procedure Parameters [...]