SQL Server stored procedure from Powershell failing to execute, it has both input and output values

My PowerShell is attempting to execute a stored procedure, passing in two input variables and expecting an output value. This code is based on another question here, but I can't get an output value, which the original question didn't ask.

Code update: I actually managed to pull this off using:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $SQLConnectionString
$SQLConnection.Open()

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "proc_InsertOrganizationSecret"
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandType = [System.Data.CommandType]'StoredProcedure';

$param1=$sqlcmd.Parameters.Add("@OrganizationId", [System.Data.SqlDbType]::Int)
$param1.Value=10

$param1=$sqlcmd.Parameters.Add("@Secret", [System.Data.SqlDbType]::NVarChar)
$param1.Value="HelloWorld"

$paramReturn = $sqlCmd.Parameters.Add("@ReturnValue", [System.Data.SqlDbType]::Int)
$paramReturn.Direction = [System.Data.ParameterDirection]::ReturnValue

$result = $SqlCmd.ExecuteNonQuery()

$returnValue = [int]$sqlCmd.Parameters["@ReturnValue"].Value

Write-Host Return value is $returnValue

$SQLConnection.Close()

When I try to call ExecuteNonQuery(), I get this error:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Procedure or function proc_InsertOrganizationSecret has too many arguments specified.

At P:\Orasi\Code\terumo_bct\MainGithubScript.ps1:149 char:1
$result = $SqlCmd.ExecuteNonQuery();
CategoryInfo : NotSpecified: (:) [], MethodInvocationException

FullyQualifiedErrorId : SqlException

Here is the PowerShell:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $SQLConnectionString
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "proc_InsertOrganizationSecret"
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandType = [System.Data.CommandType]'StoredProcedure';

$outParameter = new-object System.Data.SqlClient.SqlParameter;
$outParameter.ParameterName = "@SecretId";
$outParameter.Direction = [System.Data.ParameterDirection]'Output';
$outParameter.DbType = [System.Data.DbType]'int32';
$outParameter.Size = 2500;
$outParameter.Direction = [System.Data.ParameterDirection]'Input';

$inParameter = new-object System.Data.SqlClient.SqlParameter;
$inParameter.ParameterName = "@OrganizationId"
$inParameter.Value="10"
$inParameter.DbType = [System.Data.DbType]'int32';
$inParameter.Size = 2500;

$in2Parameter = new-object System.Data.SqlClient.SqlParameter;
$in2Parameter.ParameterName = "@Secret"
$in2Parameter.Value="HelloWorld"
$in2Parameter.DbType = [System.Data.DbType]'string';
$in2Parameter.Size = 2500;

$inParameter.Direction=[System.Data.ParameterDirection]'Input';
$in2Parameter.Direction=[System.Data.ParameterDirection]'Input';

$SqlCmd.Parameters.Add($outParameter) >> $null;
$SqlCmd.Parameters.Add($inParameter) >> $null;
$SqlCmd.Parameters.Add($in2Parameter) >> $null;

$SqlConnection.Open();

$result = $SqlCmd.ExecuteNonQuery();
$truth = $SqlCmd.Parameters["@SecretId"].Value;
$SqlConnection.Close();
$truth;

And here is the stored procedure:

CREATE OR ALTER PROCEDURE [dbo].[proc_InsertOrganizationSecret] 
    @OrganizationId int, 
    @Secret nvarchar(255)
AS
BEGIN
    -- Suppress query output
    SET NOCOUNT ON;

    DECLARE @SecretId int

    INSERT INTO dbo.OrganizationSecrets (OrganizationId, Secret) 
    VALUES (@OrganizationId, @Secret)

    SET @SecretId = SCOPE_IDENTITY()

    RETURN @SecretId
END

@SecretId is not an output parameter, although it should be. Currently its a ReturnValue, so [System.Data.ParameterDirection]::ReturnValue

But it's against TSQL coding conventions and best practices to return data through a stored procedure return value; it's a mostly-obsolete mechanism to signal success or failure of the procedure.

So the proc should be returned through an output parameter:

PROCEDURE [dbo].[proc_InsertOrganizationSecret] 
    @OrganizationId int, @Secret nvarchar(255), @SecretId int output
AS
BEGIN
    -- Supress query output
    SET NOCOUNT ON;

    DECLARE @SecretId int

    INSERT INTO dbo.OrganizationSecrets (OrganizationId, Secret) VALUES (@OrganizationId, @Secret)

    SET @SecretId=SCOPE_IDENTITY()


END

or a resultset

PROCEDURE [dbo].[proc_InsertOrganizationSecret] 
    @OrganizationId int, @Secret nvarchar(255), @SecretId int output
AS
BEGIN
    -- Supress query output
    SET NOCOUNT ON;

    DECLARE @SecretId int

    INSERT INTO dbo.OrganizationSecrets (OrganizationId, Secret) VALUES (@OrganizationId, @Secret)

    select SCOPE_IDENTITY() SecretId    

END

which you would access with SqlCommand.ExecuteScalar, or .ExecuteReader.

SQL Server stored procedure from Powershell failing to execute, it has both input and output values

Related

Recent Posts