Search code examples
sql-server-2012sp-executesql

SQL query behaving differently when run with sp_executesql

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 enter image description here

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
Solution

  • 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)
...