Okay, as identified in a comment the issue is the order of parameters. To demonstrate the issue imagine the following simple procedure
CREATE PROCEDURE dbo.TestProc @A INT = NULL, @B INT = NULL, @C INT = NULL
AS
BEGIN
SELECT TOP 1 A = @A, B = @B, C = @C;
END
GO
The key thing to remember is that there is no mapping between variables and parameters, e.g.
DECLARE @C INT = 1;
EXECUTE dbo.TestProc @C;
Does not recognise that your variable is called @C therefore you want to assign this to the parameter of the same name. If you do not explicitly allocate values to parameters when calling your procedure then they will be allocated on a first come first served basis, so the above will return:
A B C
------------------
1 NULL NULL
In fact having since your variable is nothing more than a place holder the above query is no different to just executing:
EXECUTE dbo.TestProc 1;
And you certainly would not expect the procedure to know that you intended to pass 1 as the value for @C not @A. The query your ORM is generating has a similar issue. The equivalent code with the above procedure is:
EXECUTE sp_executesql N'EXEC dbo.TestProc @A, @C', N'@A INT, @C INT', @A = 1, @C = 2;
Which returns:
A B C
------------
1 2 NULL
Now to answer some of your questions in the comment.
I do pass parameters names and types when using sp_executesql don't I?
Yes, you can, but it is not compulory, the following two statements will yield the same result:
EXECUTE sp_executesql
N'EXEC dbo.TestProc @A = @A, @B = @B, @C = @C',
N'@A INT, @B INT, @C INT',
@A = 1, @B = 2, @C = 3;
EXECUTE sp_executesql
N'EXEC dbo.TestProc @A, @B, @C',
N'@A INT, @B INT, @C INT',
1, 2, 3;
If you only want to pass certain values then it is advisable, but not compulsory to use named parameters. The difference is that if you don't use the parameter names then you must provide the parameters in the correct order:
EXECUTE sp_executesql
N'EXEC dbo.TestProc @A = @A, @C = @C',
N'@A INT, @C INT',
@A = 1, @C = 3;
EXECUTE sp_executesql
N'EXEC dbo.TestProc @A, @B, @C',
N'@A INT, @B INT, @C INT',
1, NULL, 3;
Why the proc cannot match them?
As explaned above, the procedure has no knowledge of your variable name, just its value, so the following two statements are equivalent:
DECLARE @C INT = 1;
EXECUTE dbo.TestProc @c;
EXECUTE dbo.TestProc 1;
If you examine the execution plan XML for the former query, you can confirm this:
<ParameterList>
<ColumnReference Column="@C" ParameterCompiledValue="NULL" ParameterRuntimeValue="NULL" />
<ColumnReference Column="@B" ParameterCompiledValue="NULL" ParameterRuntimeValue="NULL" />
<ColumnReference Column="@A" ParameterCompiledValue="NULL" ParameterRuntimeValue="(1)" />
</ParameterList>
Any reference to the name of the variable passed to the procedure is lost. Furthermore even if it were not lost there is no reason why it should match up, what would you have it do with mis-matches, e.g.
EXECUTE dbo.TestProc @D, @A, 2;
@A matches a paramter name, but @D does not and neither does the constant 2. There is no sensible way of sorting this out, and AFIK there is no language that infers parameters from the names of variables passed.
What does all that 4 lines of tsql code do?
1. exec sp_executesql
2. N'EXEC [PostViewProc] @Id, @AuthorId',
3. N'@Id uniqueidentifier,@AuthorId uniqueidentifier',
4. @Id='18767F6A-FF7A-47E9-AF09-6DB8A2F3B20E',@AuthorId='5455D9B9-B25A-41BD-BD2C-C9CBAE87D629'
- Line 1 is just the invokation of the procedure
sp_executesql.
- Line 2 is the statement parameter - this is the sql to be executed.
- Line 3 is the
@params parameter, this contains the definition of all the parameters used in the statement.
- Line 4 is the values for the parameters used in
@params, unless explicitly allocated (e.g. @A = 1, @B = 2) these are allocated in the same order as the parameters are defined
What more should i define to that proc to make it get what's happening here?
I don't know what ORM you are using so I am not sure how to force it to generate the right code, it should be generating:
EXEC sp_executesql
N'EXEC [PostViewProc] @Id = @Id, @AuthorID = @AuthorId',
N'@Id uniqueidentifier,@AuthorId uniqueidentifier',
@Id='18767F6A-FF7A-47E9-AF09-6DB8A2F3B20E',@AuthorId='5455D9B9-B25A-41BD-BD2C-C9CBAE87D629';
The difference being the second line has changed from
N'EXEC [PostViewProc] @Id, @AuthorId',
to
N'EXEC [PostViewProc] @Id = @Id, @AuthorID = @AuthorId',
If you cannot enforce using named parameters then you will have to pass all values in the correct order, including NULL values.
