SQL Server stored procedure not seeing parameter

Question

I'm using C# to call a stored procedure and fill it from a datatable, but I'm doing something wrong.

My stored procedure:

CREATE PROCEDURE [dbo].[getStationInfo]
    @stationList AS dbo.udtableStationCode READONLY
AS  
BEGIN
    SELECT * 
    FROM stations
    WHERE StationCode IN (SELECT * FROM @stationList) 
END

This procedure uses this user-defined table type:

CREATE TYPE [dbo].[udtableStationCode] 
     AS TABLE (StationCode NVARCHAR(50))

I'm trying to send a datatable to the stored procedure and get the result back in another datatable. Here's my C#:

    using (SqlConnection con = new SqlConnection(strConn))
    {
        con.Open();

        using (SqlCommand cmd = new SqlCommand("getStationInfo", con))
        {
            using (SqlDataAdapter ada = new SqlDataAdapter(cmd))
            {
                using (DataTable dtStationsReturned = new DataTable())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Clear();
                    cmd.CommandText = "dbo.getStationInfo";
                    cmd.Parameters.AddWithValue("@stationList", dtStationCodes);
                    ada.Fill(dtStationsReturned);
                }
            }
        }
    }

No matter what I try, when my code gets to the 'ada.Fill' line, I get the error:

The procedure "getStationInfo" has no parameter named "@stationList".

But the stored procedure getStationInfo clearly DOES have that parameter. Can anyone tell me what I'm doing wrong? Any help appreciated.

EDIT: I've checked the contents of dtStationCodes, and it's fine.

EDIT: Here is how I am creating my dtStationCodes datatable:

DataTable dtStationCodes = new DataTable();
dtStationCodes.Columns.Add("StationCode", typeof(String));

Do you get the the same error with `cmd.Parameters.Add("@stationList", SqlDbType.Structured).Value = dtStationCodes;`? [AddWithValue is evil](http://www.dbdelta.com/addwithvalue-is-evil/). — Dan Guzman, Jul 03 '18 at 01:48
Do you have multiple databases? Have you perhaps updated one but not the other? — mjwills, Jul 03 '18 at 02:08
Aw CRAP! That was it! I have multiple connections, and my code was talking to a different test server. Everyone who was kind enough to respond, I am so sorry for wasting your time. I'm the stupidest person in the world. — buckshot, Jul 03 '18 at 02:18
@buckshot this happens to best of us, keep making mistakes and keep improving. go easy on yourself — Zeeshan Adil, Jul 03 '18 at 10:08

pcdev · Answer 1 · 2018-07-03T02:18:05.647

2

Try this:

using (SqlConnection con = new SqlConnection(strConn))
{
    con.Open();
    using (SqlCommand cmd = new SqlCommand("dbo.getStationInfo", con))
    {
        using (SqlDataAdapter ada = new SqlDataAdapter(cmd))
        {
            using (DataTable dtStationsReturned = new DataTable())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Clear();
                cmd.CommandText = "dbo.getStationInfo";
                SqlParameter sp = new SqlParameter("@stationList", dtStationCodes);
                sp.SqlDbType = SqlDbType.Structured;
                sp.TypeName = "dbo.udtableStationCode";
                cmd.Parameters.Add(sp);
                ada.Fill(dtStationsReturned);
            }
        }
    }
}

Refer to this question for another example of passing user defined table types.

Also see this page for more information on creating SqlParameters and passing TVPs to a stored procedure.

edited Jul 03 '18 at 02:18

answered Jul 03 '18 at 01:53

pcdev

2,852
2
23
39

Thank you @pcdev. I'm now getting "UdtTypeName property must be set only for UDT parameters." But your suggestion seems to be a UDT parameter. Similar error, same issue...? – buckshot Jul 03 '18 at 02:00
that gives me my original error. You fixed whatever the other thing was, but the original problem remains. – buckshot Jul 03 '18 at 02:09
Problem solved, see comments to OP. Wasting everybody's time, sorry. : | – buckshot Jul 03 '18 at 02:19
Hey, these things happen! Glad you found the issue – pcdev Jul 03 '18 at 02:21

SQL Server stored procedure not seeing parameter

1 Answers1