question

I ran into a weird problem caused by the parameters passed into dynamic SQL. The dynamic SQL had parameters all through it like so: SET @sql = @sql + N'AND ( Book.ID = @paramBookID) ' IF (NOT @AuthorID IS NULL) SET @sql = @sql + N'AND (Book.AuthorID = @paramAuthorID) ' And I then passed in the parameters to the sp_executeSql call like so: -- Execute the parameterized dynamic SQL query DECLARE @params NVARCHAR(500) = N'@paramBookID int, @paramAuthorID int' EXEC sp_executesql @sql, @params, @paramBookID = @BookID, @paramAuthorID = @AuthorID The dynamic query was very slow, and at first I thought it was the dynamic SQL execution that was causing the problem, so I created the dynamic SQL and copied it over to SSMS. I then declared all the variables at the top of the query and it executed much faster (about a second, instead of 1+ minutes) I then did some more fiddling around, and replaced a line where there was a variable with the actual value of the variable: Replaced: AND (Book.AuthorID = @paramAuthorID) With: AND (Book.AuthorID = 1471409) The query started running slow again. Not sure why this caused problems and made the query run so slow. I even tried this: AND (Book.AuthorID = CAST(1471409 AS INT)) because I thought maybe it was some sort of typing issue, but that didn’t help either, it still ran slow, So in the end I ended up making a slight modification to the dynamic sql to copy all the parameters over into local variables in the dynamic SQL: SET @sql = N'DECLARE @BookId INT = @paramBookID ' + N'DECLARE @AuthorID INT = @paramAuthorID ' + @sql The query now runs WAY faster, and I still have no explanation as to why. Does anyone have any insight into this weird solution I stumbled upon?