How to return output varchar of a procedure in C #?

Question

How to return output varchar of a procedure in C #?

Navigation

#1 by (1 votes)

1

Error appearing:

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: Error converting data type varchar to int.

public static string CriarPessoa(string procedureName, 
                                 string tableName, 
                                 string nome, 
                                 double cpf)
{

       string returnStringOutput;

       SqlCommand sqlComando = ConexaoComParametro(procedureName);

       sqlComando.Parameters.Add(new SqlParameter("@nome", nome));
       sqlComando.Parameters.Add(new SqlParameter("@cpf", cpf));
       sqlComando.Parameters.Add(new SqlParameter("@outputmsg", DbType.String))
                                               .Direction = ParameterDirection.Output;

       SqlParameter outputmsg = sqlComando.Parameters.Add("@ouputmsg", DbType.String);
       outputmsg.Direction = ParameterDirection.ReturnValue;
       sqlComando.ExecuteNonQuery();
       returnStringOutput = (string)sqlComando.Parameters["@ouputmsg"].Value;    
       return returnStringOutput;

}

Procedure:

ALTER PROCEDURE [dbo].[sp_c_funcionario]

@operacao [char](1),
@nome [varchar](20),
@cpf [bigint],
@outputmsg [varchar](50) OUTPUT

AS

IF EXISTS (SELECT 1 FROM [dbo].[Funcionario] WHERE [cpf]=@cpf)
BEGIN

SET @outputmsg = 'Lamento, mas esse CPF já existe'

END
ELSE
BEGIN


IF @operacao='c'
BEGIN
    INSERT INTO [dbo].[Funcionario] ([nome], [cpf])
    VALUES (@nome,@cpf)

END 

END
RETURN
GO

sql-server stored-procedures asp.net-mvc

asked by anonymous 20.01.2017 / 21:06

1 answer

error add ssh key to account in github How to display item that was added to the shopping cart with javascript?

score 1 · Accepted Answer

Thanks for everyone's help, but I was able to solve the problem: It was just because I put "(new SqlParameter)" before, in passing the output parameter. It was as follows:

public static string CriarPessoa(string procedureName, char operacao, string nome, double cpf)
    {


        //INSTANCIO O PARÂMETRO ANTES DE PASSAR
        SqlParameter outputmsg = new SqlParameter();
        outputmsg.ParameterName = "@outputmsg";
        outputmsg.Direction = ParameterDirection.Output;
        outputmsg.DbType = DbType.String;
        outputmsg.Size = 2000;

        string returnStringOutput;

        SqlCommand sqlComando = ConexaoComParametro(procedureName);//faz conexão
        sqlComando.Parameters.Add(new SqlParameter("@operacao", operacao));
        sqlComando.Parameters.Add(new SqlParameter("@nome", nome));
        sqlComando.Parameters.Add(new SqlParameter("@cpf", cpf));
        sqlComando.Parameters.Add(outputmsg); //AGORA ESTÁ ASSIM, SEM O "(new SqlParameter) COMO NO EXEMPLO ACIMA DO CPF"

        sqlComando.ExecuteNonQuery();

        outputmsg = sqlComando.Parameters["@outputmsg"];

        returnStringOutput = outputmsg.Value.ToString();

        return returnStringOutput;

    }