Correct parameter types for select queries that use table valued functions

Question

My SQL Server 2012 contains a function Get_Schedule:

CREATE FUNCTION Get_Schedule(
    @pProject_Id   AS INT,
    @pPeriod_Start AS DATETIME2,
    @pPeriod_End   AS DATETIME2)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN (
        SELECT
            42     AS Val1,
            'Foo'  AS Val2);

Visual Studio 2015's SQL Server Object Explorer shows the function parameters with the types as defined.

However when dragging the function to the strongly typed dataset designer the DATETIME2 parameters are changed to ANSISTRING instead of System.DateTime or System.Data.SqlDbType.DateTime2.

The int parameter, @pProject_Id does have the correct type.

This happens both when using a select statement for the table adapter to access the database and when creating new a stored procedure (the procedure itself does have the expected DATATIME2 as parameter but the generated table adapter has ansistring).

How to have the correct parameter types in the generated table adapters?

Kasper van den Berg · Accepted Answer · 2015-11-24 08:40:43Z

2

After adding the query and its table adapter:

open the table adapter's properties;
expand SelectCommand; and,
edit the Parameters tot the correct type.

answered Nov 24, 2015 at 8:40

Kasper van den Berg

9,6864 gold badges52 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim F. Over a year ago

Just a warning to anyone adopting this method (which works) - if you subsequently change your query it will regenerate the command and reset the parameters back to their defaults.

Collectives™ on Stack Overflow

Correct parameter types for select queries that use table valued functions

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related