asked on

Entity framework and stored procedures

Hi.
why two different procedures but similar in their content, both with select * from table return different results for the entity framework 6.1 from database? Both have result (0) at the end. one returns an integer and the other returns a ObjectResult.

thx in advance

this is the working stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_Pessoas]
@PessoaID integer=0,
@CDTipoPessoa char(1)='',
@PessoaEmpresaID integer=0,
@NomePessoa varchar(100)='',
@Fantasia varchar(50)='',
@CodigoExterno integer=0
as

if @CodigoExterno is not null
if @CodigoExterno>0 and @PessoaID=0
       set @PessoaID=DBASE.dbo.PessoasConverteCodigoExterno(@CDTipoPessoa,@CodigoExterno)

select *
from DBASE.dbo.Pessoas (nolock)
where (@PessoaID=0 or PessoaID=@PessoaID) and
(@CDTipoPessoa='' or CDTipoPessoa=@CDTipoPessoa) and
(@PessoaEmpresaID=0 or PessoaEmpresaID=@PessoaEmpresaID) and
(@NomePessoa='' or NomePessoa like '%'+@NomePessoa+'%') and
(@Fantasia='' or Fantasia like '%'+@Fantasia+'%')
order by NomePessoa

return(0)

and this is the result as expected:
var resultado = DataContextFabrica.GetDataContext().sp_Pessoas(pessoaID, "", 0, "", "", codigoExterno).FirstOrDefault();

this is then, the not working sp:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_MYUSERS]
@Opcao char(1)='',
@PessoaID integer=0,
@Login varchar(50)='',
@Senha varchar(50)='',
@CodigoExterno integer=0

as

if @CodigoExterno is not null
if @CodigoExterno>0 and @PessoaID=0
       set @PessoaID=DBASE.dbo.PessoasConverteCodigoExterno('U',@CodigoExterno)

select *

from DBASE.dbo.PessoasLogins a (nolock),
DBASE.dbo.Pessoas b (nolock)
where a.PessoaID=b.PessoaID and
a.PessoaEmpresaID=b.PessoaEmpresaID and
b.StatusPessoa<>'9' and
((@opcao='P' and a.PessoaID=@PessoaID) or
(@opcao='L' and a.Login=@Login and a.Senha=@Senha))

return(0)

this is the result that I expected:
ObjectResult<sp_MYUSERS_Result> resultado = DataContextFabrica.GetDataContext().sp_MYUSERS(opcao, pessoaID, login, senha, codigoExterno);

and this is what I got:
Error      21      Cannot implicitly convert type 'int' to 'System.Data.Entity.Core.Objects.ObjectResult<sp_MYUSERS_Result>'      C:\Users\XXXXServicos\Main\Servicos.Dados\Repositorios\PessoasLoginRepositorio.cs      86      72      Services.Dados

Vitor Montalvão

A Stored Procedure doesn't return values. Function does.

What I can see from your code is that's a little difference. In the first example it returns the value from the FirstOrDefault() function. The second example doesn't has a function.

Deraldo Silva

ASKER

HI Vitor, thx for your attention.
but, I have others stored procs without function and they works.
The function here is to return a single object while the other is for a list.

you see this code in the second SP?
if @CodigoExterno is not null
if @CodigoExterno>0 and @PessoaID=0
set @PessoaID=DBASE.dbo.PessoasConverteCodigoExterno('U',@CodigoExterno)

if I remove the 'U' and change it to a proc parameter as in the first proc, it works. however, I dont know why!

Vitor Montalvão

I need to see the PessoasConverteCodigoExterno code. Maybe that parameter it's an output parameter and if so can only accept variables and not values.

Deraldo Silva

ASKER

Ok. that is.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[PessoasConverteCodigoExterno](
@CDTipoPessoa int,
@CodigoExterno int)
returns integer
as
begin

declare
@wCodigoInterno integer

set @wCodigoInterno=(
select top 1 a.CodigoInterno
from DBASE.dbo.PessoasMigracaoCorrelacoes a (nolock),
       DBASE.dbo.Pessoas b (nolock)
where a.CodigoInterno=b.PessoaID and
a.CDTipoPessoa=b.CDTipoPessoa and
                  a.CDTipoPessoa=@CDTipoPessoa and
                  a.CodigoExterno=@CodigoExterno)

if @wCodigoInterno is null
set @wCodigoInterno=0

return @wCodigoInterno

end

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zberteoc

This is more precisely what Victor is refering to:

set @PessoaID=DBASE.dbo.PessoasConverteCodigoExterno('U',@CodigoExterno)

first parameter is a of varchar(string) type, 'U', but it should be an integer as per function definition. If it is actually a varchar then you need to change the function definition like this:

ALTER function [dbo].[PessoasConverteCodigoExterno](
@CDTipoPessoa varchar(10),
@CodigoExterno int)

I used 10 but it should be of the same size as the column you compare it to, a.CDTipoPessoa in this case. Remember, this will work if the a.CDTipoPessoa column is a varchar as well. If not than keep it as is but make sure you pass an integer instead of a varchar.