Inquiridor
Stored Procedure não retorna valor

Pergunta
-
Bom dia, tenho uma stored procedure que executada individualmente funciona, porém quando a executo através do c#, ao passar pela linha
cmd.ExecuteNonQuery();
ela me retorna 0, sendo que deveria retornar o valor do id inserido.
SP
USE [CRM_Desenv] GO /****** Object: StoredProcedure [dbo].[SP_ScannerEmailInserir] Script Date: 02/05/2013 19:33:37 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SP_ScannerEmailInserir] ( @T0013_NOME varchar(50), @T0013_SERVIDOR varchar(50), @T0013_CONTA varchar(50), @T0013_USUARIO varchar(50), @T0013_SENHA varchar(50), @T0013_PORTA int, @T0013_TSSL int, @T0013_CRIARTICKET int, @T0013_REPOSTA_DE varchar(50), @T0013_REPOSTA_PARA varchar(50), @T0013_REPPOSTA_CC varchar(50), @T0013_REPOSTA_CCO varchar(50), @T0013_REPOSTA_CORPO varchar(max), @T0013_REPOSTA_STATUS int, @T0013_REPOSTA_ASSUNTO varchar(50), @T0013_ID bigint = null ) AS DECLARE @IDINSERIDA BIGINT BEGIN TRY IF(@T0013_ID IS NULL)/*Caso não encontre nenhum registro com o ID informado o sistema retorna o ID [0], neste caso incluir o bloco*/ BEGIN PRINT('Incluir Registro') INSERT INTO T0013_SCANNERDEEMAIL( T0013_NOME, T0013_SERVIDOR, T0013_CONTA, T0013_USUARIO, T0013_SENHA, T0013_PORTA, T0013_TSSL, T0013_CRIARTICKET, T0013_REPOSTA_DE, T0013_REPOSTA_PARA, T0013_REPOSTA_CC, T0013_REPOSTA_CCO, T0013_REPOSTA_CORPO, T0013_STATUS, T0013_REPOSTA_ASSUNTO )VALUES( @T0013_NOME, @T0013_SERVIDOR, @T0013_CONTA, @T0013_USUARIO, @T0013_SENHA, @T0013_PORTA, @T0013_TSSL, @T0013_CRIARTICKET, @T0013_REPOSTA_DE, @T0013_REPOSTA_PARA, @T0013_REPPOSTA_CC, @T0013_REPOSTA_CCO, @T0013_REPOSTA_CORPO, @T0013_REPOSTA_STATUS, @T0013_REPOSTA_ASSUNTO ) SET @IDINSERIDA = SCOPE_IDENTITY() print (@IDINSERIDA) SELECT @IDINSERIDA RETURN @IDINSERIDA END ELSE BEGIN PRINT('Atualizar Registro') UPDATE T0013_SCANNERDEEMAIL SET T0013_NOME = @T0013_NOME, T0013_SERVIDOR = @T0013_SERVIDOR, T0013_CONTA = @T0013_CONTA, T0013_USUARIO = @T0013_USUARIO, T0013_SENHA = @T0013_SENHA, T0013_PORTA = @T0013_PORTA, T0013_TSSL = @T0013_TSSL, T0013_CRIARTICKET = @T0013_CRIARTICKET, T0013_REPOSTA_DE = @T0013_REPOSTA_DE, T0013_REPOSTA_PARA = @T0013_REPOSTA_PARA, T0013_REPOSTA_CC = @T0013_REPPOSTA_CC, T0013_REPOSTA_CCO = @T0013_REPOSTA_CCO, T0013_REPOSTA_CORPO = @T0013_REPOSTA_CORPO, T0013_STATUS = @T0013_REPOSTA_STATUS, T0013_REPOSTA_ASSUNTO = @T0013_REPOSTA_ASSUNTO WHERE T0013_ID = @T0013_ID SET @IDINSERIDA = @T0013_ID SELECT @IDINSERIDA RETURN @IDINSERIDA END END TRY BEGIN CATCH print '' print 'Ocorreu um erro!' print 'Mensagem: ' + ERROR_MESSAGE() print 'Procedure: ' + ERROR_PROCEDURE() END CATCH
C#
public int Inserir(ScannerDeEmailController ScannerEmail) { string ConnSTR = ConfigurationManager.AppSettings["ConnectionString"]; ConnSTR = Cript.Decrypt(ConnSTR); SqlConnection conn = new SqlConnection(ConnSTR); string sCommand = "SP_ScannerEmailInserir"; SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = sCommand; cmd.CommandType = CommandType.StoredProcedure; //parametros de entrada cmd.Parameters.Add(new SqlParameter("@T0013_NOME", SqlDbType.Text)).Value = ScannerEmail.NOME; cmd.Parameters.Add(new SqlParameter("@T0013_SERVIDOR", SqlDbType.Text)).Value = ScannerEmail.SERVIDOR; cmd.Parameters.Add(new SqlParameter("@T0013_CONTA", SqlDbType.Text)).Value = ScannerEmail.CONTA; cmd.Parameters.Add(new SqlParameter("@T0013_USUARIO", SqlDbType.Text)).Value = ScannerEmail.USUARIO; cmd.Parameters.Add(new SqlParameter("@T0013_SENHA", SqlDbType.Text)).Value = ScannerEmail.SENHA; cmd.Parameters.Add(new SqlParameter("@T0013_PORTA", SqlDbType.Int)).Value = ScannerEmail.PORTA; if (ScannerEmail.TSSL == true) cmd.Parameters.Add(new SqlParameter("@T0013_TSSL", SqlDbType.Int)).Value = 1; else cmd.Parameters.Add(new SqlParameter("@T0013_TSSL", SqlDbType.Int)).Value = 0; cmd.Parameters.Add(new SqlParameter("@T0013_CRIARTICKET", SqlDbType.Int)).Value = ScannerEmail.CRIARTICKET; cmd.Parameters.Add(new SqlParameter("@T0013_REPOSTA_DE", SqlDbType.Text)).Value = ScannerEmail.REPOSTA_DE; cmd.Parameters.Add(new SqlParameter("@T0013_REPOSTA_PARA", SqlDbType.Text)).Value = ScannerEmail.REPOSTA_PARA; cmd.Parameters.Add(new SqlParameter("@T0013_REPPOSTA_CC", SqlDbType.Text)).Value = ScannerEmail.REPOSTA_CC; cmd.Parameters.Add(new SqlParameter("@T0013_REPOSTA_CCO", SqlDbType.Text)).Value = ScannerEmail.REPOSTA_CCO; cmd.Parameters.Add(new SqlParameter("@T0013_REPOSTA_CORPO", SqlDbType.Text)).Value = ScannerEmail.REPOSTA_CORPO; cmd.Parameters.Add(new SqlParameter("@T0013_REPOSTA_STATUS", SqlDbType.Int)).Value = ScannerEmail.REPOSTA_STATUS; cmd.Parameters.Add(new SqlParameter("@T0013_REPOSTA_ASSUNTO", SqlDbType.Int)).Value = ScannerEmail.REPOSTA_ASSUNTO; if (ScannerEmail.ID != 0) { cmd.Parameters.Add(new SqlParameter("@T0013_ID", SqlDbType.BigInt)).Value = ScannerEmail.ID; } //parametros de saida var parametroSaida = cmd.Parameters.Add("@IDINSERIDA", SqlDbType.Int); parametroSaida.Direction = ParameterDirection.ReturnValue; try { conn.Open(); cmd.ExecuteNonQuery(); } catch (SqlException ex) { throw new Exception(ex.Message); } finally { conn.Close(); } return Convert.ToInt32(parametroSaida.Value); }
O return Convert.ToInt32(parametroSaida.Value); sempre tem sido zero, alguem sabe me dizer o porque?
Todas as Respostas
-
-
-
-
-
-
-
-
O problema é que você está executando a query com o
cmd.ExecuteNonQuery();
Ele somente executa a procedure sem retornar nenhum valor. Tenta executar ela mas desse jeito:
cmd.ExecuteScalar();
Espero que tenha ajudado!
- Sugerido como Resposta Vinicius Nepomuceno Silva sexta-feira, 8 de fevereiro de 2013 00:33