“Doit déclarer la variable de table ”@name“” dans une procédure stockée

J'ai une procédure qui retourne l'erreur:

Doit déclarer la variable de table "@Propid".

Mais il est suivi par le message:

(123 ligne(s) affectée)

L'erreur s'affiche lorsque je l'exécuter avec

EXEC [dbo].[GetNeededProperties] '1,3,5,7,2,12', '06/28/2013', 'TT'

Mais qui fonctionne bien quand

EXEC [dbo].[GetNeededProperties] NULL, '06/28/2013', 'TT'

Quelqu'un peut-il m'aider?
La procédure:

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)

J'ai trouvé de solution lors de la déclaration de tableau comme ceci:

IF OBJECT_ID('#PropIDs', 'U') IS NOT NULL
  DROP TABLE #PropIDs

CREATE  TABLE  #PropIDs

Mais lors de l'exécution de la procédure à partir de C# (linq, sql), il renvoie une erreur

Montrer comment êtes-vous invoquer SP à partir de c#?
Lorsque vous créez une table temporaire avec le # de la façon dont vous avez sa en fait stockées dans sql server et doit être supprimé une fois que vous avez terminé de l'utiliser, la table temporaire que vous avez créé est une variable scalaire et est propbably inaccessible à ce point
Petite chose: lorsque vous déclarez votre SQL dynamique variable de type nvarchar, vous devez utiliser N de déclarer les littéraux de chaîne: N'Insert into... ou de ne pas utiliser l'Unicode
'1,3,5,7,2,12' est un chaîne que vous soyez de passage pour votre procédure stockée, mais le IN (....) opérateur s'attend à ce une liste de valeurs - pas une seule chaîne! Vous devriez être en utilisant un paramètre table pour votre IDENTIFIANT qui vous permet de passer en plusieurs valeurs de l'appelant dans un bon, bien défini, et alors vous n'avez pas besoin de recourir à du SQL dynamique, soit! TVP sont disponibles dans SQL Server 2008 et les plus récents.
En aparté, sauf si vous travaillez dans la base de données TempDB, vous aurez besoin de préfixe de votre table temporaire avec TempDB.. dans cette ligne IF OBJECT_ID('#PropIDs', 'U') IS NOT NULL, c'est à dire IF OBJECT_ID('TempDB..#PropIDs', 'U') IS NOT NULL

OriginalL'auteur Tom | 2013-11-28

sql sql-server

13

Le problème est que vous avez mélangé le SQL dynamique avec des non-SQL dynamique.

D'abord, la raison pour laquelle il fonctionne lorsque vous mettez une valeur NULL dans @NotNeededWPRNs est parce que quand cette variable est NULL, votre @ProductsSQL devient NULLE.

Ce que vous devez faire est soit de faire de votre @PropsIDs table une table non variable et d'une table temporaire ou une table physique.
OU
vous avez besoin d'envelopper le tout dans du SQL dynamique et de l'exécuter.

Donc le moyen le plus facile est de faire quelque chose comme ceci:
```
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)
    '
```
et les exécuter.
OU comme il est mentionné - modification @ProdIDs dans une table temporaire. (L'itinéraire que vous approchez de dans le CRÉER des #ProdIds, mais alors vous devez utiliser #ProdIDs au lieu de @ProdIDs partout dans la procédure stockée).

merci, ça fonctionne maintenant avec le temp table '#ProdIDs' . Je n'ai pas essayé la première solution, mais je vais le faire parce que je ne suis pas sûr de savoir comment il va travailler avec linq

OriginalL'auteur Allan S. Hansen
5

La raison pour laquelle vous obtenez cette erreur, c'est que le champ d'application de la table de variables est limité à un seul lot, depuis sp_executesql s'exécute dans son propre lot, il n'a pas de connaissances que vous avez déclaré dans un autre lot.

Il fonctionne lorsque vous @NotNeededWPRNs est NULL parce que la concaténation de NULL rendements NULL (sauf indication contraire), alors vous êtes juste de l'exécution:
```
exec sp_executesql null;
```
Je dirais aussi, si vous utilisez SQL Server 2008 ou plus tard veuillez envisager d'utiliser table des paramètres au lieu d'une liste délimitée par des cordes. Ce est beaucoup plus sûr et plus efficace, et valide l'entrée, si je devais passer 1); DROP TABLE dbo.Prioperties; -- comme @NotNeededWPRNs, vous pourriez vous retrouver sans un tableau de propriétés.

D'abord vous avez besoin pour créer le type (j'ai tendance à utiliser un nom générique pour réutilisabilité):
```
CREATE TYPE dbo.IntegerList TABLE (Value INT);
```
Ensuite, vous pouvez l'ajouter à votre procédure:
```
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)
```
Sur une autre note, vous devez éviter d'utiliser de la culture sensible formats de date, si possible, 06/28/2013 est clairement censé être le 28 juin dans ce cas, mais qu'en est 06/07/2013, sans DATEFORMAT, ou de la langue, comment savez-vous si ce sera lu que 6 juillet ou le 7 juin? Le meilleur format à utiliser est yyyyMMdd, il n'est jamais ambigu, même de la norme ISO au format aaaa-MM-jj peut être interprété comme yyyy-dd-MM dans certains paramètres.

merci pour toutes les suggestions - maintenant, j'ai réussi à le faire fonctionner, mais je vais revenir à cette apporter quelques améliorations

OriginalL'auteur GarethD
1

Vous changer de code :
```
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
```
Table variable déclarée en dehors de la dynamique SQL ne sera pas disponible pour le SQL dynamique.

Correct. J'essaie seulement de souligner quel était le problème. Pas de réécrire le code pour lui 🙂
Merci, maintenant il fonctionne quand je l'exécute en utilisant EXEC. Écrou j'obtiens une exception lors de l'exécution de C# maintenant Exception: System.Data.SqlClient.SqlException: Incorrect syntax near ')'. Avez-vous une idée de ce qui pourrait causer cela?
jamais l'esprit -l'erreur a été causée par le vide (mais pas nulle) paramètre de chaîne: "

OriginalL'auteur Raj

Vous pouvez éviter d'utiliser du sql dynamique par la création d'une fonction sql qui utilisent un CTE (j'ai trouvé le code ci-dessous de nombreuses années sur sqlservercentral - Amit Gaur) :

De modifier le corps de votre procs avec quelque chose comme ceci :

SELECT  p.WPRN AS ID,
p.Address  AS Address,
p.Address AS Street
  FROM [dbo].[Properties] AS p
WHERE 
   p.WPRN NOT IN ( SELECT item FROM dbo.strToTable(@NotNeededWPRNs, ','))

Ci-dessous le code sql qui transforme une chaîne de caractères dans une table :

CREATE FUNCTION [dbo].[strToTable] 
(
    @array varchar(max),
    @del char(1)
)
RETURNS 
@listTable TABLE 
(
    item int
)
AS
BEGIN

    WITH rep (item,list) AS
    (
        SELECT SUBSTRING(@array,1,CHARINDEX(@del,@array,1) - 1) as item,
        SUBSTRING(@array,CHARINDEX(@del,@array,1) + 1, LEN(@array)) + @del list

        UNION ALL

        SELECT SUBSTRING(list,1,CHARINDEX(@del,list,1) - 1) as item,
        SUBSTRING(list,CHARINDEX(@del,list,1) + 1, LEN(list)) list
        FROM rep
        WHERE LEN(rep.list) > 0
    )
    INSERT INTO @listTable
    SELECT item FROM rep

    RETURN 
END

OriginalL'auteur user3041160

Vous devez vous connecter pour publier un commentaire.