SQLCMD with Stored Procedure via batch file - get an output parameter

Question

I try to get the output parameters from a stored procedure executed with sqlcmd via a batch file. Details:

I'm executing the following stored procedure in SQL Server:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[uspCalibFail]
    @pStation_Name NVARCHAR(50),
    @responseMessage NVARCHAR(250) = '' OUTPUT
AS
BEGIN
    SET NOCOUNT ON

    SELECT [Station], [TLI], [Description]
    FROM CalibData
    WHERE Waiver_expiration_dat < GETDATE()

    IF EXISTS (SELECT TOP 1 SELECT [Station], [TLI], [Description] 
       FROM CalibData
       WHERE Waiver_expiration_dat < GETDATE()  
    BEGIN
        SET @responseMessage = 'Station devices overdue for calibration'
    END
    ELSE
        SET @responseMessage='Station OK'
END
GO

I'm using a batch file to execute the stored procedure, I use a batchfile parameter and enter it as the stored procedure input

sqlcmd -S NEMICQS-DB64 -U ***** -P ***** -Q "exec [NewDB].[dbo].[uspCalibData] @pStation_Name=$(station)" /v station="%~1"     

As I result I get the select query as a table (which is great) - but I don't get the output parameter @responseMessage value. How can I get it?

Answer 1

I'm using a batch file to execute the stored procedure, I use a batchfile parameter and enter it as the stored procedure input.

To capture the stored procedure output parameter in a T-SQL script, you'll need to declare a local variable and specify that as the stored procedure output parameter value. Untested example snippet:

-Q "DECLARE @vresponse_message nvarchar(250); exec [NewDB].[dbo].[uspCalibData] @pStation_Name=$(station),@presponse_message=@vresponse_message OUTPUT;SELECT @vresponse_message AS vresponse_message;" /v station="%~1"