The possibility to build stored procedures with table-valued parameters is a great new feature in SQL Server 2008, especially if utilized with the also newly implemented MERGE-command. Alas, there are some restrictions (TVPs have to be bound to a User-defined Table Type, have to be passed as read-only and some more).
One restriction I haven't found any mentioning about can be rather hazardous if ignored:
Table-valued parameters are not mandatory parameters.
Detailed Explanation:
If you create a stored procedure like this
create procedure [dbo].[usp_saveData]
@tvp myTVPType readonly
as
begin
...
end
you can call this procedure with or without the parameter @tvp.
declare @tmpTVP as myTVPType;
insert into @tmpTVP ...
exec dbo.usp_saveData @tmpTVP;
-- or
exec dbo.usp_saveData;
What SQL Server does in the second case, is passing an empty table of the type myTVPType to the procedure.
It does not raise an error. It runs through the whole procedure as if an empty table was provided.
This may have all kinds of unwanted consequences.
Just imagine for instance you want to utilize the MERGE-command for insert-update-delete operations, assuming that non-existent rows in the @tvp-table indicate, those rows are to be deleted. Bamm! Your whole table is truncated.
merge dbo.myTable targ
using @tvp src on src.fID = targ.fID
when matched then
update set fColInt = src.fColInt
,fColChar = src.fColChar
,fColDate = src.fColDate
when not matched by target then
insert
(fColInt
,fColChar
,fColDate)
values
(src.fColInt
,src.fColChar
,src.fColDate)
when not matched by source then
delete;
This may of course be desired behavior, but be aware that you have
no way of knowing if actually an empty table was passed or the procedure was called without any parameter.
So if this is not a desired behavior of your procedure, always be sure to include some kind of validation in your code.
declare @rowCnt int = (select count(*) from @tvp);
if @rowCnt <= 0
begin
RAISERROR
(N'No data given. Would result in complete truncation of table. This is not allowed!',
11,
1
);
RETURN -1;
end