Search code examples
sqlsql-serverexecutedynamicquery

Execute dynamic query only to get affected row count

I want to execute a dynamic query to get the affected row count. But SQL Result pane returns me the result after executing it. How to avoid returning the columns. I tried the below way.

DECLARE @Command NVARCHAR(MAX)= 'SELECT * FROM Product  WHERE ID = 12'
DECLARE @Count AS INT
EXEC sp_executesql @Command, N'@C INT OUTPUT', @C=@Count OUTPUT
  IF (@Count > 0)
   BEGIN
    EXECUTE (@Command)
   END
  ELSE
   BEGIN
    DECLARE @CatalogProduct VARCHAR(MAX) = 'SELECT p.ManufactureCode,p.PartNo,p.Size,p.ID AS ProductID,p.Name ,p.ParentProductID,p.BasePrice FROM Product.Product p WHERE p.ThruDate > GETDATE() '+@Where
            EXECUTE (@CatalogProduct)
   END
END

Issue

I want to avoid returning the null column set from the above attached image.

Solution

  • You can turn off the display, but I think a better approach is to get the count you want directly:

    DECLARE @Command NVARCHAR(MAX)= 'SELECT * FROM Product  WHERE ID = 12';

DECLARE @count AS INT;
DECLARE @CntCommand NVARCHAR(MAX);

SET @CntCommand = 'SELECT @count = COUNT(*) FROM (' + @Command + ') x)';

EXEC sp_executesql @CntCommand, N'@count INT OUTPUT', @count=@count OUTPUT;