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