Calling stored procedure from code causes error

Question

I am trying to call a stored procedure from code using .Net Core 3.0 and SQL Server 2012, but I keep getting this error:

SqlException: Incorrect syntax near the keyword 'exec'. Incorrect syntax near ')'.

My code:

var policycontacts = await _dbContext.PolicyContacts
                                     .FromSqlInterpolated($"exec dbo.spapi_contact_getbypolicy {input}")
                                     .FirstOrDefaultAsync()
                                     .ConfigureAwait(false);

SQL code:

EXEC Sp_executesql 
        N'SELECT TOP(1) [p].[ID], [p].[ActionsToTake], 
         [p].[AddedBy], [p].[BirthCountry], [p].[ContactGUID],
         [p].[ContactID], [p].[ContactStatus], [p].[DOBFormation],
         [p].[Domicile], [p].[EnhancedDueDiligence], [p].[EntityName],
         [p].[EstimatedAmountAssets], [p].[FirstName], [p].[Gender],
         [p].[HowClientMet], [p].[LastModifiedBy], [p].[LastModifiedDate],
         [p].[LastName], [p].[LastOpenDate], [p].[LastOpenedBy],
         [p].[MiddleName], [p].[Notes], [p].[OccupationBusiness],
         [p].[OnlineUser], [p].[OpeningNotes], [p].[OriginAssets],
         [p].[PEP], [p].[PEPDescription], [p].[PersonalSituation],
         [p].[RiskOverride], [p].[Sysdate] FROM (exec dbo.spapi_contact_getbypolicy @p0)AS [p]', 
              N'@p0 nvarchar(4000)', 
              @p0=N''

Complete error:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'exec'

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'

You can't select from a stored procedure like that at all. You would need to insert the output of your stored procedure to a table or temp table. Then you could select top 1 from that table. But you also need an order by when selecting top 1. — Sean Lange
Depending on how much data there is to process, you could simply insert an .AsAsyncEnumerable() after FromSqlInterpolated and switch to LINQ to Objects for the rest of the query. This would still execute the stored procedure for all results while discarding every row but the first; fixing that inefficiency would require rewriting to something that's not a stored procedure. — Jeroen Mostert
Oh wait, this is still Entity Framework, of course. Simply converting to an enumerable probably still won't work since it'll wrap the query in goo no matter what, even though that won't work for EXEC. Mapping the stored procedure in your data model to a method should allow for it, though -- the point is that the stored procedure result set is still just a result set that can be processed by clients in the same manner as regular queries, but you do need an ORM that cooperates for that. — Jeroen Mostert

QuestionGuyBob QuestionGuyBob · Accepted Answer · 2019-10-16T14:52:50

Not enough rep to leave a comment, so I have to leave you an answer. Sorry, I think this is more of a comment.

I have a "okay" recommendation, but it might not be the best answer. Depending on how complex your stored procedure is and if you really want to call it in the FROM section, then change your stored procedure into a table valued function. The reason why its a terrible recommendation is there are limitations and performance issues by using a function. I'd read up on the following articles and see which route you would like to take.

Table Valued Function Killing My Query Performance

https://blogs.msdn.microsoft.com/psssql/2010/10/28/query-performance-and-multi-statement-table-valued-functions/

Cross Apply with table valued function article: https://www.sqlshack.com/the-difference-between-cross-apply-and-outer-apply-in-sql-server/

The other option could be the one where Sean Lange provided.

Calling stored procedure from code causes error

1 Answers