Vfrdtyky

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

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

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.

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.

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