1

I'm trying to pass array of integers to stored procedure with user-defined table type. Here is script, creating the type:

SET ANSI_NULLS ON
            SET QUOTED_IDENTIFIER ON 
            CREATE TYPE dbo.TaskTypes AS TABLE (TaskType int)

And procedure:

CREATE PROCEDURE [dbo].[RemoveTasks]
                                @TaskTypes dbo.TaskTypes READONLY,
                                @personId INT
                            AS
                            BEGIN

                                SET NOCOUNT ON;
                                    UPDATE dbo.Tasks SET IsDeleted = 1 WHERE PersonId = @personId AND TaskTypes IN (SELECT nc.TaskType FROM @TaskTypes as nc)
                                SET NOCOUNT OFF;
                            END;

And the code invoking the procedure:

        var taskTypesDT = new DataTable();
        taskTypesDT.Columns.Add("TaskType");

        foreach (var taskId in taskTypes) // taskTypes is a List of enum type, I'm trying to pass their values
        {
            var row = feedTypesDT.NewRow();
            row["TaskType"] = (int)taskId;
            taskTypesDT.Rows.Add(row);
        }

        var personIdParam = new SqlParameter("@personId", personId);
        var taskTypesParam = new SqlParameter();
        taskTypesParam.ParameterName = "@TaskTypes";
        taskTypesParam.SqlDbType = SqlDbType.Structured;
        taskTypesParam.TypeName = "dbo.TaskTypes";
        taskTypesParam.Value = tskTypesDT;

        return dc.Database.ExecuteSqlCommandAsync("EXEC dbo.RemoveTasks @personId, @TaskTypes", personIdParam, taskTypesParam);

When it Executes sql I get exception: "type clash: int is incompatible with TaskTypes"

What am I doing wrong?

AlexK
  • 561
  • 7
  • 22
  • 3
    Try replacing the parameter order from the code that invokes stored procedure in the same order as of in the stored procedure. – Rex Aug 25 '17 at 12:54
  • refer answer from https://stackoverflow.com/questions/33772886/pass-table-value-type-to-sql-server-stored-procedure-via-entity-framework https://stackoverflow.com/questions/25870904/create-a-user-defined-table-type-in-c-sharp-to-use-in-sql-server-stored-procedur – Vijay Parmar Aug 25 '17 at 12:58
  • 1
    Can it be that you have 2 similar procedures, one in **dbo** schema (posted here as CREATE PROC code) and one in **iac** schema (the calling code) ? – sepupic Aug 25 '17 at 13:12
  • I've changed schema and replaced parameter order, and it works – AlexK Aug 25 '17 at 13:18

0 Answers0