It sounds like what you're after here is dynamic SQL. You can't pass variables as of a SQL statement, so you need to write our the SQL statement into it's own variable, and then execute that.
Fortunately, this isn't going to be an injection nightmare, as you've limited the input 9this nis a good thing). Something like this should work:
--Declare SQL Statement Variable
DECLARE @SQL varchar(max);
--Set value for SQL Statement Variable
SET @SQL ='Select ' + CASE @Value WHEN 'Y' THEN '' ELSE 'TOP 500 ' END + CHAR(10) +
'COUNT(*) OVER () TotalRows, Column1, Column2' + CHAR(10) +
'FROM Table;';
--Have a look at the SQl, for troubleshooting
PRINT(@SQL);
--_Execute SQL statement.
EXEC (@SQL);
Bad things CAN be done in 20 characters. Plus what happens in the future when it is changed to a varchar(NNN)??
I would ABSOLUTELY switch this around to use sp_executesql to completely avoid SQL Injection potential.
Considering that @Value is enclosed outside the Created SQL Statement, and resolved into only '' and 'TOP 500 ' by the use of the CASE expression, where would your concern be with the above example? Even using a statement like SET @VALUE = '; CREATE TABLE T;' will resolve to 'TOP 500 '.
I agree, however,if passing a pure parameter, sp_executesql is definitely the right choice.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk