Tengo un procedimiento que devuelve el error:
Debe declarar la variable de tabla "@PropIDs".
Pero es seguido con el mensaje:
(123 fila(s) afectada(s))
El error aparece cuando lo ejecuto con
EXEC [dbo].[GetNeededProperties] '1,3,5,7,2,12', '06/28/2013', 'TT'
Pero funciona bien cuando
EXEC [dbo].[GetNeededProperties] NULL, '06/28/2013', 'TT'
¿Puede alguien ayudarme? El procedimiento:
CREATE PROCEDURE [dbo].[GetNeededProperties]
@NotNeededWPRNs nvarchar(max), --string like '1,2,3,4,5'
@LastSynch datetime,
@TechCode varchar(5)
AS
BEGIN
DECLARE @PropIDs TABLE
(ID bigint)
Declare @ProductsSQL nvarchar(max);
SET @ProductsSQL = 'Insert into @PropIDs (ID)
SELECT [WPRN] FROM [dbo].[Properties] WHERE(WPRN in (' + @NotNeededWPRNs + '))'
exec sp_executesql @ProductsSQL
SELECT p.WPRN AS ID,
p.Address AS Address,
p.Address AS Street
FROM [dbo].[Properties] AS p
WHERE
p.WPRN NOT IN( SELECT ID FROM @PropIDs)
I've encontrado tipo de solución al declarar tabla como esta:
IF OBJECT_ID('#PropIDs', 'U') IS NOT NULL
DROP TABLE #PropIDs
CREATE TABLE #PropIDs
Pero al ejecutar el procedimiento desde C# (linq sql) devuelve un error
El problema es que estás mezclando SQL dinámico con SQL no dinámico.
En primer lugar - la razón por la que funciona cuando se pone NULL en @NotNeededWPRNs es porque cuando esa variable es NULL, su @ProductsSQL se convierte en NULL.
Lo que tienes que hacer es hacer tu tabla @PropsIDs una variable no-tabla y una tabla temporal o una tabla física. O necesitas envolver todo en SQL dinámico y ejecutarlo.
Así que la forma más fácil es hacer algo como esto:
Declare @ProductsSQL nvarchar(max);
SET @ProductsSQL = '
DECLARE @PropIDs TABLE
(ID bigint)
Insert into @PropIDs (ID)
SELECT [WPRN] FROM [dbo].[Properties] WHERE(WPRN in (' + @NotNeededWPRNs + '))
SELECT p.WPRN AS ID,
p.Address AS Address,
p.Address AS Street
FROM [dbo].[Properties] AS p
WHERE
p.WPRN NOT IN( SELECT ID FROM @PropIDs)
'
y ejecutar eso. O como se ha mencionado - cambiar @ProdIDs a una tabla temporal. (La ruta que usted're acercándose en el CREATE #ProdIds, pero entonces usted necesita utilizar #ProdIDs en vez de @ProdIDs por todas partes en el sproc).
La razón por la que obtienes este error es que el ámbito de las variables de tabla está limitado a un único lote, ya que sp_executesql
se ejecuta en su propio lote, no tiene conocimiento de que lo has declarado en otro lote.
Funciona cuando @NotNeededWPRNs
es NULL
porque concatenando NULL
se obtiene NULL
(a menos que se establezca lo contrario), por lo que sólo se está ejecutando:
exec sp_executesql null;
También me gustaría decir, si usted está usando SQL Server 2008 o posterior por favor considerar el uso de parámetros valorados tabla en lugar de una lista delimitada de cadenas. Esto es mucho más seguro y eficiente, y valida la entrada, si pasara 1); DROP TABLE dbo.Prioperties; --
como @NotNeedWPRNs
, podrías encontrarte sin tabla de propiedades.
Primero tendrías que crear el tipo (yo tiendo a usar un nombre genérico por reusabilidad):
CREATE TYPE dbo.IntegerList TABLE (Value INT);
A continuación, puede agregarlo a su procedimiento:
CREATE PROCEDURE [dbo].[GetNeededProperties]
@NotNeededWPRNs dbo.IntegerList READONLY,
@LastSynch DATETIME,
@TechCode VARCHAR(5)
AS
BEGIN
SELECT p.WPRN AS ID,
p.Address AS Address,
p.Address AS Street
FROM [dbo].[Properties] AS p
WHERE p.WPRN NOT IN (SELECT Value FROM @NotNeededWPRNs)
En una nota no relacionada, se debe evitar el uso de formatos de fecha sensibles a la cultura siempre que sea posible, 06/28/2013
es claramente supone que es 28 de junio en este caso, pero ¿qué pasa con 06/07/2013
, sin establecer DATEFORMAT
, o el idioma ¿cómo saber si esto va a ser leído como 6 de julio o 7 de junio? El mejor formato a utilizar es "aaaammdd", nunca es ambiguo, incluso el formato estándar ISO aaaa-MM-dd puede ser interpretado como "aaaa-dd-MM" en algunas configuraciones.
Cambia tu código a:
Declare @ProductsSQL nvarchar(max);
SET @ProductsSQL = 'DECLARE @PropIDs TABLE
(ID bigint);
Insert into @PropIDs (ID)
SELECT [WPRN] FROM [dbo].[Properties] WHERE(WPRN in (' + @NotNeededWPRNs + '))'
exec sp_executesql @ProductsSQL
La variable de tabla declarada fuera del SQL dinámico no estará disponible para el SQL dinámico.