Sql Server ODBC TVP procedure with a NULL field value

Question

Summary: How do you specify a nullable field value at a row/column index of a tabled-value parameter using odbc Fully Bound Multirow Buffer technique?

Details: I have defined a Table Type as follows:

CREATE TYPE tp_Transition AS TABLE 
(
    [Timestamp] [datetimeoffset](7) NOT NULL,
    [Value] [float] NULL,
);
GO

So Timestamp cannot be null but Value can.

Now I create a new stored procedure using this newly defined type as a Table-Valued parameter:

CREATE PROCEDURE [dbo].[sp_InsertTransition]
  @TransitionBatch AS [tp_Transition] READONLY
AS
BEGIN
    SET NOCOUNT ON
    INSERT INTO [dbo].[Transition] ([Timestamp], [Value])
        SELECT [Timestamp], [Value] FROM @TransitionBatch;
END;

To test this new procedure, I write the following code, note that for row 2 the [Value] field is null:

DECLARE @BatchTVP AS [tp_Transition];

/* Add data to the table variable. */
INSERT INTO @BatchTVP ([Timestamp], [Value])
    VALUES
    ('2014-05-14 13:05:00.000', 130500.009),
    ('2014-05-14 13:05:01.000', NULL);

/* Pass the table variable data to a stored procedure. */
EXEC [dbo].[sp_InsertTransition] @BatchTVP;

The above code works fine and value get inserted in the table as expected.

Now I want to replicate this sql code from c++ using ODBC and Table-Valued Parameter with Fully Bound Multirow Buffers (Send Data as a TVP with All Values in Memory) as described in this technet article: http://technet.microsoft.com/en-us/library/ff878030.aspx

I have read most pages of the specification but could not pinpoint the way to achieve this.

Thanks for any hint.

Frédéric

Answer 1

What you have to do is put SQL_NULL_DATA in your length buffer when you bind to the column parameter. This means you cannot use the handy SQL_NTS for null terminated strings. So you have to carry your string lengths around in a buffer. Where will the value be null? - use SQL_NULL_DATA instead of the actual length or SQL_NTS for that matter - that works as well in the buffer.