CODEWITHSUNDEEP
sql-serverpowershell
Ahmed Saeed
Ahmed Saeed

Reputation: 851

How to get the stored procedure return value using PowerShell and SQLCMD?

I have a SQL Server stored procedure that returns a result set and an integer return value. I wanted to execute it using SQLCMD and PowerShell redirecting the result set to a text file and capture the return value in a PowerShell variable.

My problem, I'm not able to capture the return value into $RC variable.

The stored procedure:

CREATE PROCEDURE [DBO].[TEST]
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @MYTABLE TABLE(PARTNO INT, TRANDATE DATE, QUANTITY INT)

  INSERT INTO @MYTABLE 
  VALUES (1, '2018-01-01', 1000), (1, '2018-02-01', 2000),
         (2, '2018-03-01', 3000)

  SELECT * 
  FROM @MYTABLE 

  RETURN @@ROWCOUNT
END

The Powershell script is something like:

Param();
Clear-Host; 

$Command = "Exec [test].[dbo].[Test]";
$RC = sqlcmd -Q $Command -s ',' -u -h-1 -W | Out-File "C:\Test.txt";
Write-Host $RC;

Thanks in advance.

Upvotes: 1

Views: 3787

Answers (1)

Basil Kisel
Basil Kisel

Reputation: 106

If a statement returns a ResultSet, SQLCMD -Q "EXIT(stmt) will leave out the return code. It's described there under the code sample sqlcmd -Q "EXIT(SELECT COUNT(*) FROM '%1') Furthermore, some exit codes of SQLCMD are reserved.

There is a workaround based on temporary file. You could find this crutch helpful.

# Control
$TMP_FILE = [System.IO.Path]::GetTempFileName() # A return code will be placed in that file.
$PROC_NAME = "testdb.dbo.test"
$SQL_BATCH = "set nocount on;
create table #ret_code_store (ret_code int); -- You have to store @ret_code in that temp table because GO will erase ret_code's variable.
declare @ret_code as int; 
exec @ret_code = $PROC_NAME;
insert into #ret_code_store values(@ret_code);
go -- You need it because it separates STDOUT from ret_code's file.
:Out $TMP_FILE
select ret_code from #ret_code_store;
:EXIT()"

# Do work
$RECORD_SET = (sqlcmd -Q $SQL_BATCH -s ',' -u -h-1 -W) # Returns a ResultSet from SP. You can proceed it as you want.
$RET_CODE = [System.Convert]::toint32((Get-Content $TMP_FILE | Select-Object -Last 1))
Remove-Item -Path $TMP_FILE # There is a limit up to 65535. Lets respect OS limitations.

# Results
Write-Output "Return: $RET_CODE`n"
Write-Output "ResultSet:`n"
Write-Output $RECORD_SET

Upvotes: 2

Related Questions