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?

4
  • 3
    Try replacing the parameter order from the code that invokes stored procedure in the same order as of in the stored procedure. Commented Aug 25, 2017 at 12:54
  • refer answer from stackoverflow.com/questions/33772886/… stackoverflow.com/questions/25870904/… Commented Aug 25, 2017 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) ? Commented Aug 25, 2017 at 13:12
  • I've changed schema and replaced parameter order, and it works Commented Aug 25, 2017 at 13:18

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.