SQL query behaving differently when run with sp_executesql

Question

I have a query in SQL Server 2012 that I first run without using sp_executesql followed by running it using sp_executesql. The strange part is that both these runs give different results, when they should be the same since they are using the same SQL query.

I have even tried using OUTPUT for @productId as in code below, but even then it made no difference. In screen shot given at end of this post, the first two result sets should repeat for the last two result sets, but they don't.

EXECUTE sp_executesql   @qry,
                        N'@maxRows int,@startingRowNumber int,@productId bigint OUTPUT',
                        @maxRows = @maxRows,
                        @productId = @productId OUTPUT,
                        @startingRowNumber = @numberOfRowsToSkip

Question: What is causing the difference in results for the same query?

DECLARE @startingRowNumber INT = 1;
DECLARE @productId BIGINT;
DECLARE @maxRows INT = 10;
DECLARE @qryCount NVARCHAR(MAX);
DECLARE @qry NVARCHAR(MAX);
DECLARE @numberOfRowsToSkip INT;
SET @numberOfRowsToSkip = @startingRowNumber - 1;

--RUN query batch without sp_executesql
SELECT  @productId = MAX(ProductId)
             FROM (SELECT TOP (@startingRowNumber)
                    ProductId
                    FROM dbo.Prods
                    WHERE [Product Cost] < 1005
                    ORDER BY ProductId ASC) x;
SELECT @productId AS ProductId;
SELECT ProductId,Product,Vendor,VendorId,[Product Cost]
FROM dbo.Prods WITH (NOLOCK)
WHERE ProductId >= @productId
AND [Product Cost] < 1005
ORDER BY [Product Cost] ASC OFFSET @numberOfRowsToSkip
ROWS FETCH NEXT @maxRows ROWS ONLY;

--RUN query batch using sp_executesql
SET @qry = N'SELECT @productId = MAX(ProductId)
             FROM (SELECT TOP (@startingRowNumber)
                    ProductId
                    FROM dbo.Prods
                    WHERE [Product Cost] < 1005
                    ORDER BY ProductId ASC) x;
SELECT @productId AS ProductId;
SELECT ProductId,Product,Vendor,VendorId,[Product Cost]
FROM dbo.Prods WITH (NOLOCK)
WHERE ProductId >= @productId
AND [Product Cost] < 1005
ORDER BY [Product Cost] ASC OFFSET @numberOfRowsToSkip
ROWS FETCH NEXT @maxRows ROWS ONLY';

EXECUTE sp_executesql   @qry,
                        N'@maxRows int,@startingRowNumber int,@productId bigint',
                        @maxRows = @maxRows,
                        @productId = @productId,
                        @startingRowNumber = @numberOfRowsToSkip
PRINT N'Executed select query'

Query Results Screenshot

UPDATE 1

The funny thing that I also noticed is that Query 1 returns a value, but Query 2 does not. I am quite sure the answer to this would be the answer to original question.

Query 1

SELECT @productId = Max(x.ProductId) FROM
(SELECT TOP (@startingRowNumber)  ProductId FROM dbo.Prods WHERE
      [Product Cost] < 1005 ORDER BY ProductId ASC) x;
SELECT @productId AS ProductId

Query 2

SET @qry = N'SELECT @productId = Max(x.ProductId) FROM
(SELECT TOP (@startingRowNumber)  ProductId FROM dbo.Prods WHERE
      [Product Cost] < 1005 ORDER BY ProductId ASC) x;';
EXECUTE sp_executesql   @qry,
                        N'@maxRows int,@startingRowNumber int,@productId bigint OUTPUT',
                        @productId = @productId OUTPUT,
                        @startingRowNumber = @numberOfRowsToSkip,
                        @maxRows = @maxRows
SELECT @productId AS ProductId

Alexei - check Codidact · Accepted Answer · 2015-12-29T22:06:14.977

1

I think your difference is caused by:

No dynamic: {OFFSET @startingRowNumber} 
Dynamic: {OFFSET @numberOfRowsToSkip} = {OFFSET @startingRowNumber - 1;}

Also, @productId is assigned in your dynamic query, but also provided as input. You should declare it inside the query and remove it from the parameters list of sp_executesql:

DECLARE @productId BIGINT
SET @qry = N'SELECT @productId = MAX(ProductId)
             FROM (SELECT TOP (@startingRowNumber)
...

edited Dec 29 '15 at 22:06

answered Dec 29 '15 at 21:34

Alexei - check Codidact

22,016
16
145
164

Thanks for pointing that out. Even that does not resolve the issue. I will edit my query. – Sunil Dec 29 '15 at 21:38
It seems that for some reason `@productId` is not being set when executed with sp_executesql, but with plain SQL query it's being set. I am quite sure the reason for this is also the reason for the whole query not working. – Sunil Dec 29 '15 at 21:47
I just added UPDATE 1. – Sunil Dec 29 '15 at 21:52
Try to remove @productId from the input parameters, as you assign it inside the query. – Alexei - check Codidact Dec 29 '15 at 21:54
When I tried with your suggestion, I get an error saying `Must declare the scalar variable "@productId"`. @productId is later used in the query in where clause and therefore this error. – Sunil Dec 29 '15 at 21:58
Ok, just add DECLARE @productId BIGINT; as first line in your dynamic query and remove it from parameters. – Alexei - check Codidact Dec 29 '15 at 22:04
I just tried that and I get this error: `Must declare the scalar variable "@productId"` – Sunil Dec 29 '15 at 22:10
I just sorted out the problem and have posted the changes I had to make. Thanks for all your help. – Sunil Dec 30 '15 at 00:57
Actually the root of the problem was what you mentioned in your initial reply. sp_executesql can be tricky and will not correctly throw back the actual problem always, and that is one of the problems with using sp_executesql. – Sunil Dec 30 '15 at 01:18

score 1 · Answer 2 · answered Dec 30 '15 at 00:57

I had a silly error in my dynamic query. The following sp_executesql resulted in correct execution. I had to change two things. Also, I didn't have to declare @productId as OUTPUT type parameter.

Changed @startingRowNumber = @numberOfRowsToSkip to @startingRowNumber = @startingRowNumber
Added another parameter of @numberOfRowsToSkip to sp_executesql

CODE that made it work

EXECUTE sp_executesql   @qry,
                    N'@maxRows int,@startingRowNumber int,@productId bigint,@numberOfRowsToSkip int ',
                    @maxRows = @maxRows,
                    @productId = @productId,
                    @startingRowNumber = @startingRowNumber,
                    @numberOfRowsToSkip = @numberOfRowsToSkip

SQL query behaving differently when run with sp_executesql

2 Answers2