Geek Answers Handbook

Sp_executesql - procedure or function expects a parameter that has not been set

I am working with Entity Framework in C # and I have a problem that I tracked down to the generated SQL statement.

The stored procedure accepts a table parameter, but this does not seem to be a problem.

In SQL Profiler, I see the following execution:

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

It leads to:

Msg 201, level 16, state 4, procedure SaveResults, line 0
Procedure or function "SaveResults" expects parameter "@resultID", which was not provided.

The definition of this procedure is:

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

Custom type:

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

What is wrong with this syntax sp_executesql? Why does he think that @resultIDhere is not transmitted properly?

+4
3

sp_executesql SQL dbo.SaveResults. T-SQL dbo.SaveResults . , . ? EXEC:

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

, :

exec sp_executesql N'

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

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

, . 2- . .

+4

.

,

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

cmd.CommandType = CommandType.StoredProcedure;

,.NET sp_executesql..., : ... , ,

execute storedprocedure @param1 = ...
+1

You will need to specify parameter mappings in your sql string for it to work. in 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 that sp_executesqlit cannot automatically associate the passed parameters with the expected parameters when executing the stored procedure, unless the mappings are specified manually in the SQL string passed

+1
source

More articles:


All Articles