"Must declare the table variable "@name"" in stored procedure

Question

I have a procedure which returns the error:

Must declare the table variable "@PropIDs".

But it is followed with the message:

(123 row(s) affected)

The error appears when I execute it with

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

But works fine when

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

Can any one help me with that? The procedure:

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 found kind of solution when declaring table like this:

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

CREATE  TABLE  #PropIDs

But when execute the procedure from C# (linq sql) it returns an error

Answer 1

The issue is that you're mixing up dynamic SQL with non-dynamic SQL.

Firstly - the reason it works when you put NULL into @NotNeededWPRNs is because when that variable is NULL, your @ProductsSQL becomes NULL.

WHat you need to do is either make your @PropsIDs table a non-table variable and either a temporary table or a physical table. OR you need to wrap everything in dynamic SQL and execute it.

So the easy way is to do something like this:

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

and execute that. OR as mentioned - change @ProdIDs to a temporary table. (The route you're approaching in the CREATE #ProdIds, but then you need to use #ProdIDs instead of @ProdIDs everywhere in the sproc).

Answer 2

The reason you get this error is that the scope of table variables is limited to a single batch, since sp_executesql runs in its own batch, it has no knowledge that you have declared it in another batch.

It works when you @NotNeededWPRNs is NULL because concatenating NULL yields NULL (unless otherwise set), so you are just executing:

exec sp_executesql null;

I would also say, if you are using SQL Server 2008 or later please consider using table valued parameters instead of a delimited list of strings. This is much safer and more efficient, and validates the input, if I were to pass 1); DROP TABLE dbo.Prioperties; -- as @NotNeededWPRNs, you could find yourself without a properties table.

First you would need to create the type (I tend to use a generic name for reusability):

CREATE TYPE dbo.IntegerList TABLE (Value INT);

Then you can add it to your procedure:

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)

On an unrelated note, you should avoid using culture sensitive date formats where possible, 06/28/2013 is clearly supposed to be 28th June in this case, but what about 06/07/2013, without setting DATEFORMAT, or the language how do you know if this will be read as 6th July or 7th June? The best format to use is yyyyMMdd, it is never ambiguous, even the ISO standard format yyyy-MM-dd can be interpreted as yyyy-dd-MM in some settings.

Answer 3

Change you code to :

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 declared outside the dynamic SQL will not be available to the dynamic SQL.

Answer 4

You can avoid using dynamic sql by creating a sql function that use a CTE (I found the code below many years ago on sqlservercentral - Amit Gaur) :

Change the body of your procs with something like this :

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

Below the sql code that transforms a string into a 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