What's wrong with this SQL Statement

Question

In reference to my question I am trying this select statement and getting error

Select * From    
(SELECT * FROM  FruitCrate WHERE FruitName IN (' +@FruitNames+ '))

WHERE FruitCrate.Eatable= 1

Error

Incorrect syntax near the keyword 'WHERE'.

Edit

USE [DataBaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[SPName]
(
    @FruitNames
)
AS

exec('SELECT  *  
    FROM FruitCrate
    WHERE FruitName IN (' +@FruitNames+ ')
    and Eatable = 1')

I will be using Strongly Typed Dataset with this stored procedure — Mathematics, Sep 09 '13 at 10:43

Robert · Answer 1 · 2013-09-09T10:41:25.843

2

You should name your subquery as below

Select * From    
(SELECT * FROM  FruitCrate WHERE FruitName IN (' +@FruitNames+ ')) tab    
WHERE tab.Eatable= 1

and you shoud use the code as dynamic sql

exec('Select * From    
    (SELECT * FROM  FruitCrate WHERE FruitName IN (' +@FruitNames+ ')) tab    
    WHERE tab.Eatable= 1')

it will work for list like 'apple','strawberry','banana'

Select * From    
(SELECT * FROM  FruitCrate WHERE FruitName IN ('apple','strawberry','banana')) tab    
WHERE tab.Eatable= 1

edited Sep 09 '13 at 10:41

answered Sep 09 '13 at 10:23

Robert

24,847
8
64
77

Although correct, I'm pretty sure this is not what op is trying to achieve. – Vatev Sep 09 '13 at 10:24
The `in` operator will not work with 1 string containing a list. – juergen d Sep 09 '13 at 10:32
@Vatev if the names looks like `'apple','strawberry','banana'` ? – Robert Sep 09 '13 at 10:40
If they are also properly escaped... I'm not familiar with sql-server, and I don't know if its easy to escape strings there. – Vatev Sep 09 '13 at 10:41
@Vatev We don't know that, op's should describe it. – Robert Sep 09 '13 at 10:43

score 2 · Accepted Answer · answered Sep 09 '13 at 10:27

2

You can't pass a comma seperated string to the in operator. And you need just 1 query if you use and for the conditions.

exec('SELECT * 
      FROM FruitCrate 
      WHERE FruitName IN (' +@FruitNames+ ')
      and FruitCrate.Eatable= 1'

If you want to execute a dynamic query (which is the case here) that you have to use exec

answered Sep 09 '13 at 10:27

juergen d

195,137
36
275
343

thanks, I am trying to create a stored procedure which will get list as parameter, so @FruitNames is a list of strings seperated by ";" – Mathematics Sep 09 '13 at 10:28
If you use `,` instead of `;` my query above will work. Otherwise you have to replace `;` with `,` – juergen d Sep 09 '13 at 10:29
But there's a problem, I need it to be in SP and exec can't used in SP if I am right – Mathematics Sep 09 '13 at 10:32
I tried and getting this error, Must declare the scalar variable "@FruitNames". – Mathematics Sep 09 '13 at 10:38
1

Please add your stored procedure header code to your question. You need an input parameter for your stored procedure that is named `@FruitNames`. The error says: *I don't have a variable named `@FruitNames` – juergen d Sep 09 '13 at 10:40
you are missing the data type of your input parameter. Example `@FruitNames varchar(1000)` – juergen d Sep 09 '13 at 10:45

score 1 · Answer 3 · answered Sep 09 '13 at 10:24

1

Try this:

Select * From    
(SELECT * FROM FruitCrate WHERE FruitName IN (' +@FruitNames+ ')) as FruitCrateRes
WHERE FruitCrateRes.Eatable= 1

You have used table name FruitCrate without rename of subquery input of your from clause

answered Sep 09 '13 at 10:24

Joe Taras

14,775
7
39
53

score 0 · Answer 4 · answered Sep 09 '13 at 10:38

Why aren't you using

SELECT * FROM  FruitCrate WHERE FruitName IN (' +@FruitNames+ ')
WHERE FruitCrate.Eatable= 1

Probably, using your sub query, SQL engine doesn't recognize FruitCrate in last WHERE, because subquery name was not specified.

Your IN clause may be misused, also!

What's wrong with this SQL Statement

4 Answers4