August 22, 2017 at 5:14 am
Hi,
declare @Value varchar (20)
SELECT @Value = case when @Status = 'Y' then '' else 'top 500 ' end
I need to append the above @Value variable result at the start to the below query.
Select @Value COUNT(*) OVER() TotalRows, Column1, Column2 from table (but this doesn't work)
I can use a if condition, but I don't know want to prolong the query. Is there anyway to achieve this?
The final query should look like this if @Status='Y'
Select top 500 COUNT(*) OVER() TotalRows, Column1, Column2 from table
August 22, 2017 at 5:53 am
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);
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 22, 2017 at 7:29 am
Thom A - Tuesday, August 22, 2017 5:53 AMIt 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.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 22, 2017 at 7:34 am
TheSQLGuru - Tuesday, August 22, 2017 7:29 AMThom A - Tuesday, August 22, 2017 5:53 AMIt 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
August 22, 2017 at 7:47 am
OOPSIE!! Coffee clearly hasn't kicked in yet! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 23, 2017 at 5:17 am
Would a simple IF/THEN/ELSE structure suffice?
IF @Status = 'Y'
BEGIN
Select Top 500 COUNT(*) OVER() TotalRows, Column1, Column2 from table
END
ELSE
BEGIN
Select COUNT(*) OVER() TotalRows, Column1, Column2 from table
END
August 23, 2017 at 8:33 am
TOP allows an expression sin SQL 2008
TOP(CASE WHEN @Status = 'Y' THEN 500 ELSE 999999 END)
Far away is close at hand in the images of elsewhere.
Anon.
August 24, 2017 at 11:37 am
You can use variable as numeric number and it should work, and for else part use some higher number. With this you can avoid dynamic query
declare @i int =case when @Status = 'Y' then 1000000 else 500 end
Select top (@i) COUNT(*) OVER() TotalRows, Column1, Column2 from table
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply