0

I'm working with SQL Server 2014 and I'm trying to execute a (dynamic) stored procedure from entity framework.

My stored procedure has the following input parameters:

@Username          NVARCHAR(100),
@Number            DECIMAL,
@PageIndex         INT = 1,
@PageSize          INT = 20,
@Field1            NVARCHAR(12) = NULL,
@Field2            NVARCHAR(60) = NULL,
@Field3            NVARCHAR(60) = NULL,
@Field4            NVARCHAR(60) = NULL,
@Field5            NVARCHAR(60) = NULL,
@Field6            NVARCHAR(60) = 'ABC',
@Field7            NVARCHAR(4)  = 'ABC',
@IsCountOnly       BIT = 0,
@IsFilterOnly      BIT = 0

EF generates the following query (captured via SQL profiler) based on the parameters I've added to my array but note that I'm only adding the ones I need since most of them are optional.

exec sp_executesql 
N'EXEC MySp @Username, @Number, @IsCountOnly', 
N'@Username nvarchar(100), Number decimal(6,0), @IsCountOnly bit', 
@Username=N'me@mycompany.com', @Number=12345, @IsCountOnly=1

When I pass the @Username, @Number which are both compulsory and then pass the @IsCountOnly, my stored procedure gets executed but it returns the wrong results as it seems to treat the value passed via @IsCountOnly as @PageIndex rather than @IsCountOnly.

Note that if I call EXEC directly in the SQL Server Management Studio:

EXEC    [dbo].[MySp]
@Username = N'me@mycompany.com',
@Number = 12345,
@IsCountOnly = 1
GO

It works as expected.

To test whether this was the problem, I included all the parameters in the exec sp_executesql N'EXEC ... and it worked as expected but it just seems like an overkill having to defined all the parameters when only a few are needed in some scenarios.

Is this a bug in SQL when working with exec sp_executesql and EXEC or am I doing something wrong??

Thanks.

UPDATE-1:

Note that when I say it worked as expected when passing all the parameters, that's not entirely true as when I pass null for the optional parameters, it's not actually using the default values provided which to some extend makes sense but it means I would have to pass the correct defaults in my .NET project rather than via SQL which is just not ideal.

Thierry
  • 6,142
  • 13
  • 66
  • 117
  • 2
    You're doing it wrong (sorry). Use `EXEC MySp @Username = @Username, @Number = @Number, @IsCountOnly = @IsCountOnly` instead. It's confusing, but your syntax actually passes parameters by ordinal position, just filling those ordinal values with the parameters of the parameterized `EXEC` statement itself. – Jeroen Mostert Jun 27 '19 at 11:26
  • 1
    None of the parameters are specifying which parameter they are passing, so they are based on ordinal position, not name of the variable. The 3rd parameter in your SP is `@PageIndex`, so `@IsCountOnly` (which is the 3rd value passed), will be treated as the value for `@PageIndex`; and the remainder will have their default value. This seems like a mapping error in your application code. – Thom A Jun 27 '19 at 11:26
  • *"when I pass null for the optional parameters, it's not actually using the default values provided"* A default value for a parameter is used when the parameter is omitted, not when `NULL` is passed. `NULL` is still a value, just an unknown one. – Thom A Jun 27 '19 at 11:57
  • @JeroenMostert you were bang on! Quickly modified my function that creates the field list for my optional fields to include the same field and it worked immediately!! Thanks – Thierry Jun 27 '19 at 13:20

1 Answers1

1

You can use named parameters:

N'EXEC MySp @Username=@Username, @Number=@Number, @IsCountOnly=@IsCountOnly''
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I'm not generating this code. Entity framework is and this is my problem. as it is generating the query as 'exec sp_executesql N'EXEC....' as mention in question which is causing the problem. This is the call I have in my data layer: `DbContext.Database.SqlQuery("EXEC MySP @Username, @Number, " + optionalParameters, parameterList.ToArray())` where the optional parameters are just additional @Field being set and the parameterList contains an array of SqlParameters. – Thierry Jun 27 '19 at 11:39
  • I'm going to try to call it via .SqlQuery and see what happens just by hardcoding it first and not pass the parameters array as it is optional – Thierry Jun 27 '19 at 11:43
  • It doesn't work unfortunately. as sp_executesql expects parameters to be passed. – Thierry Jun 27 '19 at 11:52
  • @Thierry: see [this question](https://stackoverflow.com/q/4873607/4137916) for more on how EF rolls specifically. – Jeroen Mostert Jun 27 '19 at 12:01
  • As @JeroenMostert stated, using name parameters is the solution. Thanks again. – Thierry Jun 27 '19 at 13:21