sp_executesql - Procedure or function expects parameter which was not supplied

7
votes

I am working with Entity Framework in C# and am having an issue which I have tracked down to the SQL statement being generated.

The stored procedure takes in a table-valued parameter, but this doesn't seem to be the issue.

In SQL Profiler, I am seeing the following being executed:

declare @p3 dbo.PositiveTypes
insert into @p3 values(N'1')
insert into @p3 values(N'4')
insert into @p3 values(N'6')

    exec sp_executesql N'dbo.SaveResults',
                       N'@resultID int, @positiveResults [PositiveTypes] READONLY',
                         @resultID=1,
                         @positiveResults=@p3

This results in:

Msg 201, Level 16, State 4, Procedure SaveResults, Line 0
Procedure or function 'SaveResults' expects parameter '@resultID', which was not supplied.

The definition of this procedure is:

ALTER PROCEDURE [dbo].[SaveResults] 
    @resultID int, 
    @positiveResults AS dbo.PositiveTypes READONLY

User defined type is:

CREATE TYPE [dbo].[PositiveTypes] AS TABLE(
    [TypeID] [tinyint] NULL
)

What is wrong with this sp_executesql syntax? Why does it think that @resultID is not being passed properly here?

3
sql-servertsqlstored-procedures
How are you executing this? Is there no way of setting the command type so EF knows it is a stored procedure? - Martin Smith
It's being executed in EF using Database.ExecuteSqlCommand on the DbContext. It seems to recognize it as a stored procedure based on the error message from SQL Server ("procedure or function" ... expects "parameter"). I am trying to determine exactly what is wrong with the sp_executesql syntax and then work backwards to rework the code as needed. - Patrick
Ah, OK. I thought there might be some command type equivalent but doesn't appear to be the case - Martin Smith

3 Answers

8
votes

I had the same exact issue.

Once you declare the command, you have to specify the command type

SqlCommand cmd = new SqlCommand(@"sp_name", con);

cmd.CommandType = CommandType.StoredProcedure;

If you don't do this, .NET will generate a command for using sp_executesql... and that is the problem is ... you specify the command type as above and the code generated is using 

execute storedprocedure @param1 = ...
4
votes

You're using sp_executesql to run the SQL text dbo.SaveResults. This T-SQL runs the procedure dbo.SaveResults with no parameters. Now you understand where that message comes from. What to do about it? Use EXEC:

EXEC dbo.SaveResults @resultID = 1234, @positiveResults = @p3

Or, nest the call:

exec sp_executesql N'

    EXEC dbo.SaveResults @resultID = @resultID, @positiveResults = @positiveResults

',N'@resultID int, @positiveResults [PositiveTypes] READONLY',@resultID=1,@positiveResults=@p3

I have indented to make it more clear. The 2nd variant is not useful as far as I can tell. Use the first one.

1
votes

You will have to specify the parameter mappings in your sql string for it to work. i.e in the C# code replace

db.Database.SqlQuery<T>("EXEC dbo.SaveResults", resultId, positiveResults)

with

db.Database.SqlQuery<T>("EXEC dbo.SaveResults @resultId=@resultId, @positiveResults=@positiveResults", resultId, positiveResults)

It seems sp_executesql is unable to automatically associate the passed parameters to the expected parameters when executing a stored procedure unless the mappings are manually specified in the SQL string passed