Table valued parameters in SQL Server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 99 of sequel server
tutorial in this video well discus table
valued parameters in sequel server table
valued parameter is a new feature
introduced in sequel server 2008 table
valued parameters allow a table that is
multiple rows of data to be passed as a
parameter to a stored procedure a the
from transact sequel code or from an ad
or alternate application prior to sequel
server 2008 it is not possible to pass a
table variable as a parameter to a
stored procedure one important thing to
keep in mind here is that table valued
parameters must be passed as read-only
to sequel server routines like stored
procedures functions etc this means
you'll not be able to perform any DML
operations like insert update or delete
on a table valued parameter in the body
of a function store procedure etc let's
understand with an example how to pass a
table variable as a parameter to a store
procedure for this example we'll be
using this employee's table notice it
has got three columns ID name gender at
the moment this employee's table does
not have any data within it now we want
to write & insert stored procedure that
insert stored procedure should be
capable of inserting multiple rows into
this employee's table every time we call
that stored procedure the way we are
going to achieve that is by passing
table variable as a parameter to that
stored procedure let's look at the steps
involved the first step is to create a
user-defined table type so let's do that
we create a user-defined at table type
using create type statement and I'm
going to name our user-defined table
type EMP table type this is going to be
user defined table type so as stable now
we have to define the structure for this
table the structure of this user define
the table type is going to be very
similar to this employee's table so I am
simply going to copy these columns from
the employees table into our
user-defined table type so let's execute
this create type statement so where can
I find this user-defined table type that
we have just created notice I have
executed this create
statement within the context of sample
DB so I expand sample DB database and
then programmable day and then types and
then user-defined table types here we
can find the user-defined table type
that we have just created so that's the
first tab create user-defined table type
the second step is to pass that
user-defined table type as a parameter
Toa insert stored procedure so let's
create our insert stored procedure now
create procedure let's call it SP insert
employees and this study is going to
have an input parameter I'm going to
call this @ EMP table type so the data
type of this input parameter is going to
be our user-defined table type which is
EMP table type so here we are passing a
table variable as a parameter to over
insert store procedure as begin and so
what do we want to do here insert into
employee's table select star from our
table variable and our table variable
here is @ EMP table type now one
important thing to keep in mind is that
table valued parameters must be passed
as read-only to stored procedures
functions etc that means you will have
to use this read-only keyword now let's
go ahead and create this procedure now
what do you think is going to happen if
we don't use that read-only keyword
let's look at that look at this when I
create this procedure the error message
says the table valued parameter at EMP
table type must be declared with the
read only option okay so let's uncomment
this and now when we clear this
procedure that should be successfully
created so that's our second step the
third step is to declare a table
variable insert the data rows that we
want to pass to the stored procedure
into that table variable and then
pass that table variable as a parameter
to the store procedure so let's look at
that in action now so the last and final
step is to declare a variable so let's
call this at employee table type that's
the name of our variable and the
datatype for this one is going to be our
user-defined table type which is EMP
table type so we have the variable here
the table variable now into this table
variable we are going to insert data
that we want to pass to the store
procedure so I'm going to use insert
into @ employee table type values and
then we will have to specify the values
for ID name and gender so let's say IDs
1 name is Mart and gender is Mel let's
insert four more rows in the interest of
time I have already typed the required
insert queries so this is the data that
we want to send that to our store
procedure the final step is to call the
stored procedure and that stored
procedure Passover table variable as a
parameter so execute the name of our
procedure is SP insert employees and to
this we are going to pass our table
variable as a parameter now before we
execute this code let's select the data
from employees table just to make sure
we don't have any data within that and
if you look at how many rows we have in
our table variable we are passing five
rows so now when we execute this code
all these five rows should be inserted
into employee's table
okay so let's hit f5 and now when we
select the data from employees table we
should have five rows there so here we
have the first and second step and here
is the third step in this video we have
seen how to pass a table variable as a
parameter to a stored procedure from the
transact cecal code in our next video
we'll discuss how to do the same from an
ad or net applica
Asian thank you for listening and have a
great day