Using a global temporary table in this scenario could cause problems as the table would exist between sessions and may result in some problems using the calling code asynchronously. A local temporary table can be used if it defined before calling sp_executesql e.g. CREATE TABLE #tempTable(id int); execute sp_executesql N’INSERT INTO #tempTable SELECT myId FROM … Read more
tl;dr update statistics We had a delete query with one parameter (the primary key) that took ~7 seconds to complete when called through EF and sp_executesql. Running the query manually, with the parameter embedded in the first argument to sp_executesql made the query run quickly (~0.2 seconds). Adding option (recompile) also worked. Of course, those … Read more
Here is a simple example: EXEC sp_executesql @sql, N’@p1 INT, @p2 INT, @p3 INT’, @p1, @p2, @p3; Your call will be something like this EXEC sp_executesql @statement, N’@LabID int, @BeginDate date, @EndDate date, @RequestTypeID varchar’, @LabID, @BeginDate, @EndDate, @RequestTypeID