CODEWITHSUNDEEP
c#sql-server-2008ado.net
ligos
ligos

Reputation: 4266

SQL Server - passing a table valued parameter with computed columns

How do you pass a table valued parameter to a stored procedure with a computed column?

I have a table data type which I'm trying to pass a DataTable to a stored proc. I've got all my columns matching up in order and data type except for a computed column.

If I leave that column out of my DataTable I get this error: System.Data.SqlClient.SqlException (0x80131904): Trying to pass a table-valued parameter with 3 column(s) where the corresponding user-defined table type requires 4 column(s).

If I include the column with null values I get this: System.Data.SqlClient.SqlException (0x80131904): The column "TransformationSum" cannot be modified because it is either a computed column or is the result of a UNION operator.

Haven't tried fiddling with any of the various properties of the particular DataColumn yet.

Table type definition:

CREATE TYPE [dbo].[DataPoint] AS TABLE 
(
    [Id] [int] NOT NULL,
    [RawDataPoint] [decimal](18, 9) NULL,
    [TransformedDataPoint] [decimal](18, 9)  NULL,
    [TransformationSum]  AS ([RawDataPoint]-[TransformedDataPoint]),

    PRIMARY KEY ([Id])
)

And the stored proc definition:

CREATE PROCEDURE [report].[spRptBubblePlot]
    @pData [dbo].[DataPoint] READONLY, 
    ....
AS
BEGIN
    ....
END

And my code which sets the parameter:

    var parm = cmd.CreateParameter();
    parm.ParameterName = "@pData";
    parm.SqlDbType = SqlDbType.Structured;
    parm.TypeName = "[dbo].[DataPoint]";
    parm.Value = myDataTable;
    cmd.Parameters.Add(parm);

(Note: everything works fine without the computed column.)

Upvotes: 6

Views: 7230

Answers (1)

Icarus
Icarus

Reputation: 63962

AFAIK, You can't modify table-valued parameters inside a stored procedure. If you need to perform a calculation and return it from within the proc, you need to pass the table-valued parameter without the column with null values, create a copy with an identical structure plus the extra column you need and return the new table with the extra column or as many extra columns you need.

Upvotes: 1

Related Questions