Sql server Query : Conditional Select columns with TOP keyword

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

, +

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 21-May-14 2:14am

senthilmonster

Updated 21-May-14 19:17pm

v6

Add a Solution

5 solutions

Solution 1

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

Posted 21-May-14 2:24am

nilschaudhari

Updated 21-May-14 2:30am

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+! :)

Solution 2

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!

Posted 21-May-14 2:25am

Kornfeld Eliyahu Peter

Solution 3

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 *"[^].

Posted 21-May-14 2:28am

Sander Rossel

Solution 4

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

Posted 21-May-14 19:29pm

Thava Rajan

Updated 21-May-14 19:30pm

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..

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

senthilmonster · Accepted Answer · 2014-05-22T01:14:00

Solution 5

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

Posted 22-May-14 1:14am

senthilmonster