2

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

Clovis Dufour
  • 21
  • 4
  • It looks like you may have omitted to create a table type for your list parameter. Check this question and answer for more details: https://stackoverflow.com/questions/5595353/how-to-pass-table-value-parameters-to-stored-procedure-from-net-code – Kell Sep 06 '17 at 08:13
  • I edited my message to add information about my stored procedure, I don't think the problem comes from there. On the other hand, I see in the topic that you sent me that I am missing the assignment of the "parameter.TypeName", but I do not know where I am supposed to fill in this field – Clovis Dufour Sep 06 '17 at 08:53
  • 1
    Hmm, it looks like it cant be done in linq to sql: https://stackoverflow.com/questions/1019414/linq-to-sql-with-stored-procedures-and-user-defined-table-type-parameter I'm not really sure if it's been implemented. As far as I know linq to sql is a dead technology and MS wants you to use EF. I generally use ado.net as it supports everything. – Kell Sep 06 '17 at 09:37

0 Answers0