Click here to Skip to main content
15,887,812 members
Articles / Database Development / SQL Server / SQL Server 2008
Tip/Trick

Beware of empty Table-valued Parameters

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
5 Mar 2010CPOL1 min read 30.1K   4   2
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)


Written By
Database Developer
Ireland Ireland
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionTVPs Pin
professordavos15-Oct-14 21:03
professordavos15-Oct-14 21:03 
GeneralMy vote of 5 Pin
Enrique Albert9-Nov-12 0:59
Enrique Albert9-Nov-12 0:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.