Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server / SQL-Server-2008

Beware of empty Table-valued Parameters

5.00/5 (1 vote)
5 Mar 2010CPOL1 min read 1  
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...
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
SQL
create procedure [dbo].[usp_saveData]
    @tvp myTVPType readonly
as
begin
...
end

you can call this procedure with or without the parameter @tvp.
SQL
declare @tmpTVP as myTVPType;
insert into @tmpTVP ...
exec dbo.usp_saveData @tmpTVP;

-- or
SQL
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.
SQL
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.
SQL
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)