„Trebuie declarată variabila de tabel „@name”” în procedura stocată (Programare, Sql, Server Sql)

Tom a intrebat.

Am o procedură care returnează eroarea:

Trebuie declarată variabila de tabel „@PropIDs”.

Dar aceasta este urmată de mesajul::

(123 rând(uri) afectat(e))

Eroarea apare atunci când o execut cu

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

Dar funcționează bine atunci când

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

Mă poate ajuta cineva cu asta?Procedura:

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)

Am găsit un fel de soluție atunci când am declarat un tabel ca acesta:

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

CREATE  TABLE  #PropIDs

Dar atunci când execut procedura din C# (linq sql), aceasta returnează o eroare.

Comentarii

  • Arată cum invoci SP din c#? –  > Por Anatolii Gabuza.
  • Atunci când creezi un tabel temporar cu # așa cum ai făcut-o de fapt este stocat în sql server și trebuie șters după ce nu-l mai folosești, tabelul temporar pe care l-ai creat este o variabilă scalară și este probabil inaccesibilă în acel moment – -.  > Por liquidsnake786.
  • Un lucru mic: atunci când declarați variabila SQL dinamică ca nvarchar, ar trebui să utilizați N pentru a declara literali de tip string: N'Insert into... sau nu va folosi Unicode –  > Por Szymon.
  • '1,3,5,7,2,12' este un un singur șir de caractere pe care îl treceți în procedura stocată, dar IN (....) se așteaptă ca operatorul o listă de valori – nu un singur șir de caractere! Ar trebui să folosiți un parametru cu valoare de tabel pentru ID-urile dvs., ceea ce vă permite să transmiteți mai multe valori de la apelant într-un mod corect și bine definit, iar atunci nu trebuie să recurgeți la SQL dinamic! TVP-urile sunt disponibile în SQL Server 2008 și mai noi. –  > Por marc_s.
  • Ca o paranteză, cu excepția cazului în care lucrați în TempDB, va trebui să prefixați tabelul temporar cu TempDB.. în această linie IF OBJECT_ID('#PropIDs', 'U') IS NOT NULL, , de ex. IF OBJECT_ID('TempDB..#PropIDs', 'U') IS NOT NULL –  > Por GarethD.
4 răspunsuri
Allan S. Hansen

Problema este că amestecați SQL dinamic cu SQL nedinamic.

În primul rând – motivul pentru care funcționează atunci când puneți NULL în @NotNeededededWPRNs este pentru că atunci când acea variabilă este NULL, @ProductsSQL devine NULL.

Ceea ce trebuie să faceți este fie să faceți din tabelul @PropsIDs o variabilă non-table și fie un tabel temporar, fie un tabel fizic. SAU trebuie să înfășurați totul în SQL dinamic și să îl executați.

Așadar, modul cel mai simplu este să faceți ceva de genul acesta:

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)
    '

și executați-o. SAU, după cum am menționat, schimbați @ProdIDs într-un tabel temporar. (Calea pe care o abordați în CREATE #ProdIds, dar atunci trebuie să folosiți #ProdIDs în loc de @ProdIDs peste tot în sproc).

Comentarii

  • mulțumesc, acum funcționează cu tabelul temporar ‘#ProdIDs’ . Nu am încercat prima soluție, dar o voi face pentru că nu sunt sigur cum va funcționa cu linq –  > Por Tom.
GarethD

Motivul pentru care primiți această eroare este că domeniul de aplicare al variabilelor de tabel este limitat la un singur lot, deoarece sp_executesql se execută în propriul lot, nu are cunoștință de faptul că ați declarat-o în alt lot.

Funcționează atunci când @NotNeededWPRNs este NULL deoarece concatenarea NULL produce NULL (dacă nu se stabilește altfel), deci nu faceți decât să executați:

exec sp_executesql null;

Aș mai spune, de asemenea, dacă folosiți SQL Server 2008 sau o versiune ulterioară vă rugăm să accesați luați în considerare utilizarea parametrii valorizați în tabel în loc de o listă delimitată de șiruri de caractere. Acest lucru este mult mai sigur și mai eficient și validează datele de intrare, dacă ar fi să trec 1); DROP TABLE dbo.Prioperties; -- ca @NotNeededWPRNs, , v-ați putea trezi fără un tabel de proprietăți.

Mai întâi ar trebui să creați tipul (am tendința de a folosi un nume generic pentru reutilizare):

CREATE TYPE dbo.IntegerList TABLE (Value INT);

Apoi îl puteți adăuga la procedura dvs:

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)

În altă ordine de idei, ar trebui să evitați, pe cât posibil, să folosiți formate de date sensibile la cultură, 06/28/2013 este în mod clar 28 iunie în acest caz, dar ce se întâmplă cu 06/07/2013, , fără a seta DATEFORMAT, sau limba, de unde știți dacă va fi citit ca fiind 6 iulie sau 7 iunie? Cel mai bun format care trebuie utilizat este yyyyMMdd, , nu este niciodată ambiguu, chiar și formatul standard ISO yyyy-MM-dd poate fi interpretat ca fiind yyyy-dd-MM în anumite setări.

Comentarii

  • mulțumesc pentru toate sugestiile – acum am reușit să fac să funcționeze, dar voi reveni asupra acestui lucru pentru a face unele îmbunătățiri –  > Por Tom.
Raj

Schimbați-vă codul în :

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

Variabila de tabel declarată în afara SQL dinamic nu va fi disponibilă pentru SQL dinamic.

Comentarii

  • Corect. Încercam doar să subliniez care era problema. Nu rescriam codul pentru el 🙂 –  > Por Raj.
  • Mulțumesc – acum funcționează atunci când îl execut folosind EXEC. Nut Primesc o excepție atunci când îl execut din C# acum Exception: System.Data.SqlClient.SqlException: Incorrect syntax near ')'. Aveți vreo idee ce ar putea cauza asta? –  > Por Tom.
  • nu contează – eroarea a fost cauzată de un parametru de șir gol (dar nu nul): „” – –  > Por Tom.
user3041160

Puteți evita utilizarea sql-ului dinamic prin crearea unei funcții sql care utilizează un CTE (am găsit codul de mai jos cu mulți ani în urmă pe sqlservercentral – Amit Gaur) :

Schimbați corpul procs-ului dvs. cu ceva de genul acesta :

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, ','))

Mai jos codul sql care transformă un șir de caractere într-un tabel :

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