DataSet returns error "Must declare the scalar variable ..."

Question

I'm trying to add a query to my dataset and getting an error "Must declare the scalar variable @searchstr". I find this a little odd because I've used the @variable to pass parameters before no problem, but for whatever reason it fails here.

select DISTINCT g.groupname, 
CASE WHEN s.siteguid = @searchstr THEN 1 ELSE 0 END AS doesitexist
from groups g left outer join sitegroups as sg on g.groupguid = sg.groupguid 
left outer join sites as s on sg.siteguid = s.siteguid

Found it fails in LINQPad as well, so add a little more info, though sadly this still doesn't work in a DataSet. :-( The below works in LINQPad, and SQL Server Management Studio.

declare @searchstr nvarchar(64);
set @searchstr = '21EC2020-3AEA-1069-A2DD-08002B30309D';
select DISTINCT g.groupname, 
CASE WHEN s.siteguid = @searchstr THEN 1 ELSE 0 END AS doesitexist
from groups g left outer join sitegroups as sg on g.groupguid = sg.groupguid 
left outer join sites as s on sg.siteguid = s.siteguid

What am I doing wrong that's tripping me up? Suspect WHEN is tripping me up?

Answer 1

Perhaps it's not incorrect in the sql, but at another location (Parameter definition, etc.)?

Answer 2

It's probably confused because you're not using the parameter in the WHERE statement. At a glance this doesn't look like something you should be doing at the SQL Server anyway as it doesn't affect the specific rows returned.

Maybe give a bit more info on what you actually want to achieve with this SQL?