C#•4y ago

❔ SQL Server | Can i use 'Case When' in Where Clause?

i ususally do it by create the query into the string like this

set @sql = 'select * from person p .....'
set @where = ' where xxx = xxx'

IF @role <> 'ALL'
BEGIN
  SET @where= @where + ' AND p.role IN ('''+@ProductId+''')'
END

SET @query = @sql +  @where
EXECUTE(@query)

set @sql = 'select * from person p .....'
set @where = ' where xxx = xxx'

IF @role <> 'ALL'
BEGIN
  SET @where= @where + ' AND p.role IN ('''+@ProductId+''')'
END

SET @query = @sql +  @where
EXECUTE(@query)

but it realyyyy hard to debug if the query is very long,
so anyone know the proper way to do it?

© 2026 Hedgehog Software, LLC

More

Communities Docs About Terms Privacy

set @sql = 'select * from person p .....'
set @where = ' where xxx = xxx'

IF @role <> 'ALL'
BEGIN
  SET @where= @where + ' AND p.role IN ('''+@ProductId+''')'
END

SET @query = @sql +  @where
EXECUTE(@query)

set @sql = 'select * from person p .....'
set @where = ' where xxx = xxx'

IF @role <> 'ALL'
BEGIN
  SET @where= @where + ' AND p.role IN ('''+@ProductId+''')'
END

SET @query = @sql +  @where
EXECUTE(@query)