Executing stored procedure with input param in C#

-1

I am having trouble executing a stored procedure supplying string value for a input param that is of type nvarchar(128). The param in question is 'username' . As per the proc definition is nvarchar(128) please look at screenshot#2. Below is my C# code where am setting the parameters

if (StoredProcedureExists(auditStoredProc, localConnString))
{
    try
    {
        using (SqlCommand auditCommand = new SqlCommand("sp_executesql", localConnection, tran))
        {
            auditCommand.CommandType = CommandType.StoredProcedure;
            auditCommand.CommandTimeout = sqlCommandTimeoutFallbackValue;
            auditCommand.Parameters.AddWithValue("@stmt", auditStoredProc);
            var userNameParam = auditCommand.CreateParameter();
            userNameParam.ParameterName = "@username";
            userNameParam.SqlDbType = SqlDbType.NVarChar;
            userNameParam.Value = "JQS";
            auditCommand.Parameters.Add(userNameParam);

            //my custom method to show what command will be executed
            var commnd = CommandAsSql(auditCommand);

            auditCommand.ExecuteNonQuery();
        }
    }

    catch (Exception e)
    {

    }
}

The command execution query built by the code is

use my_db_name_here;
declare @return_value int;
exec [sp_executesql]
@stmt = 'dbo.pr_kr_AuditSetUserName', 
@username = 'JQS';
select 'Return Value' = convert(varchar, @return_value);

When it executes, the exception I see is

'Incorrect Syntax near JQS'

Full Exception message: e = {"Incorrect syntax near 'JQS'."}

"System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'JQS'.\r\n at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)\r\n
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action
1 wrapCloseInAction)\r\n
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)\r\n
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)\r\n
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)\r\n
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource
1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)\r\n at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()\r\n at Endpoint.JQS.Common.Processor.ProcessJob(JobQueueMessage message) in C:\dev\Endpoint.JQS\Endpoint.JQS.Common\Processor.cs:line 92\r\n ClientConnectionId:39dbe3e4-8b0e-4d23-8b13-2bc379313328\r\n Error Number:102,State:1,Class:15"

NOTE: Ln#92 in the error message is the line that calls to auditCommand.ExecuteNonQuery();

Stored procedure dbo.pr_kr_AuditSetUserName definition is below input parameter type proc definition

c#
tsql
stored-procedures
asked on Stack Overflow May 8, 2019 by theandroid • edited May 8, 2019 by Chris Catignani

1 Answer

0

I replaced the "sp_execute_sql" with the stored procedure name itself. That also removes the need to pass the stored proc name in @stmt parameter. The working code is below.

try
{
  using (SqlCommand auditCommand = new SqlCommand(
                                         auditStoredProc,
                                         localConnection,
                                         tran))
  {
     auditCommand.CommandType = CommandType.StoredProcedure;
     auditCommand.CommandTimeout = sqlCommandTimeoutFallbackValue;
     var userNameParam = auditCommand.CreateParameter();
     userNameParam.ParameterName = "@username";
     userNameParam.SqlDbType = SqlDbType.NVarChar;
     userNameParam.Value = "JobQueueService";
     auditCommand.Parameters.Add(userNameParam);
     auditCommand.ExecuteNonQuery();
  }
}
catch (Exception e)
{
   Logger.Error("Error executing audit storedprocedure" + e.ToString());                                            
}
answered on Stack Overflow May 13, 2019 by theandroid

User contributions licensed under CC BY-SA 3.0