This is a direct follow on from my previous post really as one of the most common uses for the Table Type is that you can use it (in SQL Server 2008 onwards) to pass a table as a parameter into a Stored Procedure. This is incredibly useful as one of the things that I see most often asked on the internet is how to pass tabular data to a stored procedure.
People come up with all manner of solutions for this from global temp tables or comma separated lists to XML, but this is by far the easiest in my opinion.
Let’s just use a very basic example to show the technique…
Firstly we’ll create a table type:
where name = 'demoType'
drop type demoType
create type demoType as table
id int identity,
myOtherValue decimal(10, 2)
Okay, now let’s create a (somewhat pointless) procedure which accepts the table type as a parameter:
if object_id('testProc') is not null drop procedure testProc
create procedure testProc
@tableParam demoType readonly
declare @myTable demoType
insert into @myTable
select myDate, myValue, myOtherValue
set myDate = dateadd(n, myValue, myDate)
set myOtherValue = (myValue * 2.00) / datepart(n, mydate)
order by id
The one thing to note here is that if you pass a table into a stored procedure as a parameter then the table MUST be read only and therefore you cannot edit it.
Therefore, because I’m editing the data in my proc, I have simply declared a new table using the table type, loaded it with data, and then edited and returned that.
Finally we’ll call the procedure and see that it returns as expected:
declare @newTable demoType
insert into @newTable
values(current_timestamp, 1, 0),
(current_timestamp, 12, 0),
(current_timestamp, 34, 0),
(current_timestamp, 2, 0),
(current_timestamp, 45, 0)
exec testProc @newTable
And there you are… you’ve passed tabular data to a stored proc using a table type. Simples.