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

sql, sql-server

Solution

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

Problem

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

Original source

Related problems