C# Linq to SQL pass List in stored procedure

1.9k Views Asked by At

I'm looking to pass a List of string in my stored procedure, I tried to set up the indications of the following tutorial but I'm stuck in last step: https://larrymcnutt.wordpress.com/2014/11/05/passing-a-list-int-from-c-to-a-t-sql-stored-procedure/

I created a user-defined type like this in my database :

create type dbo.myList as table
(
    element nvarchar(22)
)
go

In my class, I adapt my code with the right type and the List

new SqlMetaData("element", SqlDbType.NVarChar, 22)

But the problem I encounter is that my stored procedure return multiple values, so i use a method like this :

    [Function(Name = "dbo.MyStoredProcedure_S")]
    [ResultType(typeof(Value))]
    [ResultType(typeof(Structure))]
    [ResultType(typeof(Error))]
    public IMultipleResults MyStoredProcedure_SMultipleResults(
        [Parameter(Name = "id", DbType = "Int NOT NULL")] int id,
        [Parameter(Name = "myList", DbType = "Structured")] myCollection myList)
    {
        var result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), id, myList);
        return (IMultipleResults)(result.ReturnValue);
    }

I have not found a resource indicating how to pass my list as a parameter in this case.

The exception returned :

The table type parameter '@p1' must have a valid type name.

I also tried to change the type of my variable by a simple list of string like this

[Parameter(Name = "myList", DbType = "Structured")] List<string> myList

but I have the same result

Thanks

EDIT: this is my code used for the stored procedure :

CREATE PROCEDURE [dbo].[myStoredProcedure]
    @id int,
    @myList myList READONLY,
AS
BEGIN
    SELECT ...
    WHERE ref IN (SELECT element FROM @myList)
    ...
END

And I manage to exec my stored procedure like this :

DECLARE @return_value int
DECLARE @list [dbo].[myList]
INSERT INTO @list VALUES ('stringval'), ('otherstringval');

EXEC    @return_value = [dbo].[sp_ATL_RechercheHuilesMagasin_S]
    @id = 1,
    @myList = @list

SELECT  'Return Value' = @return_value

It returns the data in my 3 tables corresponding to the results "Value", "Structure", "Error" defined in my c# code

0

There are 0 best solutions below