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?

All your SELECT are broken: first of all, they are missing a FROM clause to define what table to read from, and the SELECT inside the IF EXISTS() is wrong because you have SELECT twice where you need it just once ..... — marc_s
– marc_s, Commented Jan 14, 2018 at 14:23
Fixed the FROM clauses, but I don't think it's relevant for the question...unless I'm completely off. — DarkLight
– DarkLight, Commented Jan 14, 2018 at 19:20

Dan Guzman · Accepted Answer · 2018-01-14 15:27:29Z

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"

answered Jan 14, 2018 at 15:27

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related