LINQ stored procedure

-1

I'm calling a stored procedure through entityframework but I get an exception

When passing the parameters

exp="tblAdquiridosGe"

_cFiltros="'d.cNombre = ''DE'''"

 return contexto.uspBusquedaCliente(exp,_cFiltros)

But when I run the query directly in SQLSERVER it throws me information or simply if you do not find anything, it does not return anything.

Am I passing the values correctly?

Thank you in advance.

USE [GESTORIGECE]
GO
/****** Object:  StoredProcedure [dbo].[uspBusquedaCliente]    Script Date: 22/08/2018 11:51:06 a. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspBusquedaCliente]
@Tablas VARCHAR(MAX),
@cFiltros VARCHAR(MAX)
AS

DECLARE @tblTablas TABLE
(
       cNombreTabla VARCHAR(200),
       bChecked BIT 
)


DECLARE @tblFiltros TABLE
(
       cFiltro VARCHAR(200),
       bChecked BIT 
)

INSERT INTO @tblTablas
SELECT Item, 0 FROM  [dbo].[udf_SplitString] (@Tablas, ',')

INSERT INTO @tblFiltros
SELECT Item, 0 FROM dbo.udf_SplitString (@cFiltros, '|')
DECLARE @SQL NVARCHAR(MAX) = '', @cTabla VARCHAR(MAX), @Where VARCHAR(MAX) = ' 1 = 1', @cFiltro VARCHAR(200)

WHILE EXISTS (SELECT TOP 1 1 FROM @tblFiltros WHERE bChecked = 0)
BEGIN
       SELECT TOP 1 @cFiltro = cFiltro FROM @tblFiltros WHERE bChecked = 0

       SET @Where = @Where + ' AND ' + @cFiltro

       UPDATE @tblFiltros
       SET bChecked = 1
       WHERE cFiltro = @cFiltro

END

WHILE EXISTS (SELECT TOP 1 1 FROM @tblTablas WHERE bChecked = 0)
BEGIN
       SELECT TOP 1 @cTabla = cNombreTabla FROM @tblTablas WHERE bChecked = 0

       IF LEN(@SQL) > 0
       BEGIN
             SET @SQL = @SQL + '
                    UNION 
                    '
       END

       SET @SQL = @SQL + 'SELECT m.cNombre as Municipio, l.cNombre as Localidad,d.cNombre as [Nombre del propietario],d.dSuperficie as Superficie,d.cRegion as Región,d.cManzana as Manzana,d.cSuperManzana as [Super Manzana], cLote,d.cClaveCatastral as [Clave Catastral],d.cEtiqueta as [Etiqueta del Expediente]
                           FROM tblDocumentos d
                           INNER JOIN ' + @cTabla + ' t1
                                 ON d.iIdDocumento = t1.iIdDocumento 
                           INNER JOIN tblCatLocalidades l
                                 ON d.iIdLocalidad = l.iIdLocalidad
                           INNER JOIN tblCatMunicipios m
                                 On l.iIdMunicipio = m.iIdMunicipio
                           WHERE ' + ISNULL(@Where, '')
       UPDATE @tblTablas
       SET bChecked = 1
       WHERE cNombreTabla = @cTabla

END

EXEC sp_executesql @SQL 

STORE

USE [GESTORIGECE] GO / ****** Object: StoredProcedure [dbo]. [UsbBusquedaCliente] Script Date: 08/22/2018 11:51:06 a. m. ****** / SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo]. [UscClick Search] @Tablas VARCHAR (MAX), @cFilters VARCHAR (MAX) AS

DECLARE @tblTablas TABLE (        cName VARCHAR (200),        bChecked BIT )

DECLARE @tblFilters TABLE (        c VARCHAR filter (200),        bChecked BIT )

INSERT INTO @tblTablas SELECT Item, 0 FROM [dbo]. [Udf_SplitString] (@Tablas, ',')

INSERT INTO @tblFiltros SELECT Item, 0 FROM dbo.udf_SplitString (@cFilters, '|') DECLARE @SQL NVARCHAR (MAX) = '', @cTabla VARCHAR (MAX), @Where VARCHAR (MAX) = '1 = 1', @cFilter VARCHAR (200)

WHILE EXISTS (SELECT TOP 1 1 FROM @tblFilters WHERE bChecked = 0) BEGIN        SELECT TOP 1 @cFilter = cFilter FROM @tblFilters WHERE bChecked = 0

   SET @Where = @Where + ' AND ' + @cFiltro

   UPDATE @tblFiltros
   SET bChecked = 1
   WHERE cFiltro = @cFiltro

END

WHILE EXISTS (SELECT TOP 1 1 FROM @tblTablas WHERE bChecked = 0) BEGIN        SELECT TOP 1 @cTabla = cNameTable FROM @tblTablas WHERE bChecked = 0

   IF LEN(@SQL) > 0
   BEGIN
         SET @SQL = @SQL + '
                UNION 
                '
   END

   SET @SQL = @SQL + 'SELECT m.cNombre as Municipio, l.cNombre as Localidad,d.cNombre as [Nombre del propietario],d.dSuperficie as Superficie,d.cRegion as Región,d.cManzana as Manzana,d.cSuperManzana as [Super Manzana], cLote,d.cClaveCatastral as [Clave Catastral],d.cEtiqueta as [Etiqueta del Expediente]
                       FROM tblDocumentos d
                       INNER JOIN ' + @cTabla + ' t1
                             ON d.iIdDocumento = t1.iIdDocumento 
                       INNER JOIN tblCatLocalidades l
                             ON d.iIdLocalidad = l.iIdLocalidad
                       INNER JOIN tblCatMunicipios m
                             On l.iIdMunicipio = m.iIdMunicipio
                       WHERE ' + ISNULL(@Where, '')
   UPDATE @tblTablas
   SET bChecked = 1
   WHERE cNombreTabla = @cTabla

END

EXEC sp_executesql @SQL

    
asked by david vazquez 22.08.2018 в 18:15
source

1 answer

0

But if you are using EntityFramework, do you do CodeFirst? or DbFirst ?. You could complement the USP with DbFirst, and bring them with entity to the project. Then simply use them from the context and that's it. you would not have to build so many things.

using (var context = new EF_DataBaseEntities())
            {
                var id = new ObjectParameter("id", typeof(long));
                context.USP_CUSTOMER_CREATE(data.Name, data.Address, data.BirthDate.ToDateTime(), data.DocumentId, data.DocumentType, data.CityId, id);
                context.SaveChanges();

                response.Code = StatusCode.Ok;
                response.Data = long.Parse(id.Value.ToString());
                response.Message = $"Usuario creado correctamente con ID {response.Data}";
            }
    
answered by 22.08.2018 в 20:18