Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Passing a Table to a Stored Procedure
Published: Oct 16, 2016
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:

if exists
(
      
select name
      
from sys.table_types
      
where name = 'demoType'
)
begin
       drop type
demoType
end

create type
demoType as table
(
      
id int identity,
      
myDate smalldatetime,
      
myValue smallint,
      
myOtherValue decimal(10, 2)
)
go


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
go

create procedure testProc
(
      
@tableParam demoType readonly
)
as
       declare
@myTable demoType
      
insert into @myTable
      
select myDate, myValue, myOtherValue
      
from @tableParam

      
update @myTable
      
set myDate = dateadd(n, myValue, myDate)

      
update @myTable
      
set myOtherValue = (myValue * 2.00) / datepart(n, mydate)

      
select *
      
from @myTable
      
order by id
go


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
go


And there you are… you’ve passed tabular data to a stored proc using a table type. Simples.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.