Execute dynamic query only to get affected row count

Question

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

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

I don't see where the variable `@where` is actually defined. — Tim Biegeleisen, Jul 10 '18 at 11:08
If you want the count use `COUNT(*)`. *Don't* use string concatenation to create queries, that exposes you to SQL injection attacks, conversion issues and bugs. SQL is dynamic already - you can simply run any query you want. — Panagiotis Kanavos, Jul 10 '18 at 11:08
Are you asking how to discard the results in SSMS? In that case Query-->Query Options-->Results-->Grid-->Discard results after execution. — Dan Guzman, Jul 10 '18 at 11:09
Why this question? What is the actual problem you want to solve? It's probably a lot easier to solve *that*. For example, paging code typically wraps a `select count(*) from (originalquery)` around a query to get the total number of rows for display. Using LINQ, it's trivial to call `.Count(*)` on any query to execute it and get the number of results without actually returning the results — Panagiotis Kanavos, Jul 10 '18 at 11:11
BTW if you wanted to execute a query only if there are any products with an ID of 12 you could specify that as a condition in the query itself. Either explicitly to return only the product with ID 12 or as an `EXISTS (select 1 from Product where ID=12)` — Panagiotis Kanavos, Jul 10 '18 at 11:12

score 1 · Accepted Answer · edited Jun 07 '19 at 20:42

1

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;

edited Jun 07 '19 at 20:42

Vnge

1,295
25
49

answered Jul 10 '18 at 11:08

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Jul 10 '18 at 11:10

Why not simply?

IF (SELECT COUNT(*) FROM Product = 12) > 0 BEGIN...

I can't see why the COUNT statement needs to be dynamic; there's nothing dynamic about it.

Also, having the SQL '... WHERE p.ThruDate > GETDATE() '+@Where is a terrible idea. If @where is a parameter it'll be wide open to SQL injection.

score 0 · Answer 3 · answered Jul 10 '18 at 11:10

0

Try this one. Returns number of rows affected by the last query: select @@Rowcount

answered Jul 10 '18 at 11:10

Meow Meow

637
6
17

Avinash · Answer 4 · 2018-07-10T11:22:23.527

DECLARE @Command NVARCHAR(MAX)= 'SELECT * FROM Product  WHERE ID = 12'
DECLARE @CountCommand NVARCHAR(MAX)= 'SELECT @Count=count(1) FROM Product  WHERE ID = 12'
DECLARE @Count AS INT
EXEC sp_executesql @CountCommand , N'@Count INT OUTPUT', @Count=@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

Execute dynamic query only to get affected row count

4 Answers4