CODEWITHSUNDEEP
c#asp.netsqlsql-server
Deviney
Deviney

Reputation: 131

SQL query not working due to error

I am trying to use a internal SQL command to query a table and use the result to query another but am getting the following problem

"Msg 137, Level 16, State 1, Line 10 Must declare the scalar variable "@ListofPropIDs"."

Any help would be appreciated

DECLARE @ListofPropIDs TABLE(IDs VARCHAR(100));
INSERT INTO         @ListofPropIDs 
SELECT              Id 
FROM                Property
WHERE               CreatedBy = '[email protected]' 

SELECT          
    COUNT           (Digits)
    FROM            dbo.CallInfo
    WHERE           Digits = @ListofPropIDs; 

GO

Upvotes: 0

Views: 70

Answers (4)

huMpty duMpty
huMpty duMpty

Reputation: 14460

You can't use a table as where condition value

Instead you can use SQL: IN Condition

SELECT          COUNT (Digits)
FROM            dbo.CallInfo
WHERE           Digits IN (Select Id  From  @ListofPropIDs)

Or use Inner Join 

  SELECT          COUNT (Digits)
  FROM            dbo.CallInfo C
                  Inner Join @ListofPropIDs T On T.Id=C.Digits

Upvotes: 0

mohan111
mohan111

Reputation: 8865

DECLARE @ListofPropIDs TABLE(IDs VARCHAR(100));
INSERT INTO         @ListofPropIDs 
SELECT              Id 
FROM                Property
WHERE               CreatedBy = '[email protected]' 

SELECT          
    COUNT           (Digits)
    FROM            dbo.CallInfo C
    INNER JOIN @ListofPropIDs s
    on s.IDs = c.Digits

Upvotes: 1

Zein Makki
Zein Makki

Reputation: 30022

@ListofPropIDs is a table and you're using it like a Value.

use :

SELECT          
    COUNT           (Digits)
    FROM            dbo.CallInfo
    WHERE           Digits IN ( SELECT IDs from @ListofPropIDs)

GO

Upvotes: 0

Ric
Ric

Reputation: 13248

without knowing what you want to achieve, something like this should work but may not be correct:

DECLARE @ListofPropIDs TABLE(IDs VARCHAR(100));
INSERT INTO         @ListofPropIDs 
SELECT              Id 
FROM                Property
WHERE               CreatedBy = '[email protected]' 

SELECT          
    COUNT           (Digits)
    FROM            dbo.CallInfo
    WHERE           Digits in (SELECT IDs FROM @ListofPropIDs)

Your original query is trying to use a table variable as part of a where clause as if it where a column which is not correct.

Upvotes: 0

Related Questions