sp_executesql - Procedure or function expects para

问题:

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?

回答1:

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.

回答2:

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 = ...

回答3:

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