Click here to Skip to main content
15,902,938 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
SQL
DECLARE @mode INT; 

SELECT CASE 
		WHEN @mode = 0
			THEN t.Column1,Count(t.Column2) as Column2
		ELSE top 1 t.Column1,Count(t.Column2) as Column2 
		END
FROM Table1 t
--Where some list of parameters
Group by t.Column1,t.Column2

Please read the above sql statement carefully. I have requirement to evaluate the query by two modes without changing the body of the query ie. From,Where and Group clauses should be written only once and not to replicate them (each one) anywhere in the result query

if @mode = 0 then the above said columns should be return and If @mode <> 0 then top 1 * of records

Both select conditions are uses the same given set list of parameters.
When I run the above query am facing the error "Incorrect syntax near the keyword 'top'." because we could not use the top 1 keyword within conditional select statements and select condition's columns must be matched even with their datatypes.

Please fix the above query without affecting logic of the query.
Posted
Updated 21-May-14 19:17pm
v6

Hello,

Create Stored Procedure for the same. Use the If Condition in the procedure as follows

SQL
Create procedure procedurename(parameters)
DECLARE @mode INT;
SET @mode = 0;

IF @mode = 0 then
     SELECT t.Column1,Count(t.Column2) as Column2
FROM Table1 t
--Where some list of parameters
Group by t.Column1,t.Column2
 else Select top 1 * 
  FROM Table1 t
--Where some list of parameters
Group by t.Column1,t.Column2
End IF
 
Share this answer
 
v2
Comments
Sander Rossel 21-May-14 8:31am    
My 5. You're suggesting the same solution as me, but you just beat me to posting it :)
Aarti Meswania 21-May-14 8:47am    
5+! :)
You can not keep this format.
The main problem isn't the top clause, you can replace it with some thing like
SQL
   else (select top 1 * from Table1)
end

But! you can not select more than one field in such a case-when structure (in the first part the second field is computed!), so you can't use * in the inner select anyway!
 
Share this answer
 
How about just using an IF statement and returning two different results?
SQL
IF @mode <= 0
BEGIN
    SELECT t.Column1, COUNT(t.Column2) AS Column2
    FROM Table1 t
    GROUP BY t.Column1
END
ELSE
BEGIN
    SELECT TOP 1 * FROM Table1
END
By the way, it's not best practice to use SELECT * in production code.
I've written an alternative to a Tip/Trick on why SELECT * is evil: The Evil That is "Select *"[^].
And here is the original: The Evil That is "Select *"[^].
 
Share this answer
 
Writing a good sql script is an art, i don't know what is your original intention to do such a script,
Anyway i will suggest you way better and concise
SQL
DECLARE @mode INT;
SET @mode = 0;
DECLARE @sql nvarchar(4000)
SET @sql = '  ' + char(10)
         + 'SELECT ' + char(10)
         + '        '+ CASE  WHEN @mode=0 THEN +'t.Column1,COUNT(t.Column2) AS Column2 ' + char(10)
          ELSE + 'TOP 1 t.Column1, COUNT(t.Column2) AS Column2 ' + char(10)  END
         + '        ' + char(10)
         + '        ' + char(10)
         + 'FROM Table1 t ' + char(10)
         + '--Where some list of parameters ' + char(10)
         + 'GROUP BY t.Column1,t.Column2 ' + char(10)
         + ''
EXEC(@sql)

but when you use a top operator always use the order by clause otherwise the output will be unpredictable
hope you got it :) happy coding
 
Share this answer
 
v2
Comments
senthilmonster 22-May-14 7:19am    
It's a good solution and it's not suitable when I have large counts Quries which are needs to be modified eg. Report project and loses the readability in some time.need better..
SQL
DECLARE @mode INT;
Set @mode  = 1 --for Min set of records
--Set @mode  = 100 --for Max / full set of records
SELECT TOP (@var) PERCENT * t.Column1 ,Count(t.Column2) AS Column2 FROM Table1 t
--Where some list of parameters
GROUP BY t.Column1  ,t.Column2
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900