You need to surround the fruits with '

i.e. '''Orange'',''Guava'''

DECLARE @SQL AS VARCHAR(MAX)

DECLARE @OrderList AS VARCHAR(MAX) = '''Orange'',''Guava'''

SET @SQL =

'SELECT Fruit,Price

FROM dbo.TESCOS

WHERE Fruit IN (' + @OrderList + ')'

SELECT @SQL