T-SQL Stored Procedure Get No Result

Question

Is there a way to suppress all results from a stored procedure result set?

That has both a EXEC and a SELECT statements.

Edit: Let me explain my problem a little bit more.

The EXEC Statement does a DTSRun that pulls data from ERP and MySQL Servers into temp tables in SQL. Once the data is in the temp tables. There is a Select statement that brings back the merged data to the user. The user is an MS Access Database that sends it through to the actual customer on via email. All triggered by a webpage button on the customer side with a "Get Report".

I want to suppress the first EXEC statement result set because I don't want the customer to see the DTS output.

Otherwise, just suppress all then have the MS Access execute the stored procedure then just pull the SELECT statement itself.

There are probably 10 better ways to do what I am doing. But I just wanted to know how to suppress the result set of a EXEC statement while still running it.

Answer 1

SET FMTONLY ON;
GO
EXEC sp_who;

However, it has side effects and weird issues. For example, in SQL Server 2012 at least, if your procedure has a #temp table, you get an invalid object name error. Erland Sommarskog goes into a lot more detail here (just search the page for FMTONLY).

Given your updates (I had no idea you were trying to suppress the output of a DTSRun call), I think a better answer is: add a flag to your stored procedure, and then depending on the flag value, use the NO_OUTPUT argument for xp_cmdshell:

ALTER PROCEDURE dbo.Whatever
  @SuppressDTSOutput BIT
AS
BEGIN
  SET NOCOUNT ON;

  ...

  IF @SuppressDTSOutput = 1
  BEGIN
    EXEC master..xp_cmdshell 'DTSRun.exe whatever', NO_OUTPUT;
  END
  ELSE
  BEGIN
    EXEC master..xp_cmdshell 'DTSRun.exe whatever';
  END

  ...
END
GO