0

I have a parameterized query shown below that is supposed to return info about items that have a name like the parameter. However, it only works for exact matches

BEGIN
SET NOCOUNT ON
DECLARE @name varchar(50)
SET @name = 'bananas'
SELECT category, sum(netweight) AS NetWeight from PieChart group by 
category, name HAVING name LIKE @name
END

Since 'bananas' is in the database, it returns that info. If it put 'banan', it returns nothing.

Thanks for your help!

coder123
  • 21
  • 2

2 Answers2

1

You can also put the wildcards in the WHERE clause:

BEGIN
    SET NOCOUNT ON;

    DECLARE @name VARCHAR(50) = 'bananas';

    SELECT  category ,
            SUM(netweight) AS NetWeight
    FROM    PieChart
    WHERE   [name] LIKE '%' + @name + '%'
    GROUP BY category ,
            [name]
END
Russell Fox
  • 5,273
  • 1
  • 24
  • 28
0

You need wildcards. In addition, you should use where, not having. So:

BEGIN
    SET NOCOUNT ON

    DECLARE @name varchar(50);

    SET @name = 'banan%';

    SELECT category, sum(netweight) AS NetWeight 
    FROM PieChart
    WHERE name LIKE @name
    GROUP BY category, name; 
END;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I think it should be `SET @name = '%banan%';` the OP does not say it should **start** with `banan`. – Ilyes Nov 15 '17 at 23:05
  • You are right thanks! Question: why use Where and not Having? they both seem to work – coder123 Nov 15 '17 at 23:08
  • @coder123 The answer is https://stackoverflow.com/questions/287474/whats-the-difference-between-having-and-where – Ilyes Nov 15 '17 at 23:11
  • @Sami . . . If "bananas" matches, but "banan" does not, then the wildcard is only needed at the end of the pattern. – Gordon Linoff Nov 15 '17 at 23:14