-1

When I used sqlcmd, It retured messages N rows affected + results. I only want to return messages N rows affected. Do you know if we make some config for sqlcmd?

CN Trường
  • 1
  • 1
  • 2
    Asking question like this will not help you. Better provide code snippets/ screenshots. https://stackoverflow.com/help/how-to-ask – Code_Tech May 29 '18 at 10:02
  • Suppressing only the row count is easy, suppressing only the results is not. You'll need to modify your queries to not return results, or pipe the result to (the moral equivalent of) `tail -1` or `findstr "rows affected"`. – Jeroen Mostert May 29 '18 at 10:04
  • That doesn't really make sense to me. If you're performing a `SELECT` statement why would you **not** want the results? What's the point in a `SELECT` statement if you don't want the results? it would be like performing an `INSERT` statement but not wanting the rows to be inserted. – Thom A May 29 '18 at 10:32
  • Change your query to `SELECT ... INTO #temp` – Squirrel May 29 '18 at 10:37

1 Answers1

0

I'm going to guess this is an xy question, and i'm basing this answer on what i'm guessing are the real OP's needs; that they are checking the number of rows in a table/statement, and are then checking the output of the return message statement to ensure it meets to expectation.

I think, instead, then, that the right way for the OP's odd set up would be to use SET NOCOUNT ON and then create your own PRINT statement. This is overly simplified SQL, however, as i have no query to work with. Firstly, a sample table:

USE Sandbox;
Go
--Create sample table
CREATE TABLE #test (i int);
INSERT INTO #test
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9);
GO

And then the solution:

--The solution
SET NOCOUNT ON; --This turns the normal message off

DECLARE @Count int;

SELECT @Count = COUNT(*)
FROM #test
WHERE i > 5;

IF @Count = 1 BEGIN
    PRINT '(' + CONVERT(varchar(5),@Count) + ' row affected)';
END ELSE BEGIN
    PRINT '(' + CONVERT(varchar(5),@Count) + ' rows affected)';
END

And a clean up

--Clean up
GO
DROP TABLE #test;
Thom A
  • 88,727
  • 11
  • 45
  • 75