case statement in sql 2005

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

in my query in where condition i am filtering one column name campus.so if campus is 5 then select the record with campus as 5 otherwise select all records.how to write query?

query like:

SQL

select code,name from table1 where campus=(//here i want to implement the logic)

Posted 19-Feb-13 0:14am

josh-jw

Updated 19-Feb-13 0:15am

v3

Add a Solution

Comments

Ankur^\m/ 19-Feb-13 7:02am

" if campus is 5 then select the record with campus as 5 otherwise select all records"
What do you mean here? Is campus a column of table1 or does it belong to other table?
If it is, 'select code,name from table1 where campus=5' will give you all rows with column = 5.
Similarly, 'select code,name from table1 where campus <> 5' will give you all rows with column not equal to 5.

josh-jw 19-Feb-13 7:19am

i want to retrieve all the records with a condition that in where clause like if my input parameter @campus is '' select all the record.otherwise Campus=@campus

Ankur^\m/ 19-Feb-13 7:44am

Use the 'Reply' link beside a comment to reply to a post. Otherwise the poster doesn't get notified and he may never see your reply.

Ankur^\m/ 19-Feb-13 7:46am

Are you using a stored procedure to execute this query?

4 solutions

Solution 4

Hi,
You can try this style as well:

DECLARE @campus INT = (
            SELECT campus
            FROM   table1
            WHERE  campus = 5
        )

SELECT code,
       NAME
FROM   table1
WHERE  campus = COALESCE(@campusId, '') = ''
       OR  campus = @campus

Posted 19-Feb-13 23:42pm

Khorshed Alam, Dhaka

Solution 5

Hi ,

Try like this

SQL

DECLARE @Campus INT =5

SELECT code,name
FROM table1
WHERE (Code=(CASE WHEN @Campus=5 THEN @Campus ELSE 0 END) OR @Campus <> 5)

Regards,
GVprabu

Posted 20-Feb-13 0:15am

gvprabu

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)

S. M. Ahasan Habib · Accepted Answer · 2013-02-19T06:25:00

Better you go for dynamic sql.Suppose you have a department table which has 2 columns(Id, Name)
The code is as follows

SQL

declare @Id int = 3;
declare @sql nvarchar(max) = 'SELECT Id,Name FROM Departments';

IF @Id = 2
   SET @sql += ' WHERE Id='+ cast(@id as varchar(10));

EXEC sp_ExecuteSQL @sql;

Maciej Los · Accepted Answer · 2013-02-19T07:16:00

Solution 3

Create a stored procedure, like this:

SQL

CREATE PROCEDURE GetCamousByID
    @id INT
AS
BEGIN

    DECLARE @qry NVARCHAR(300)

    SET @qry = 'SELECT * FROM table1 '
    IF (@id = 5)
        SET @qry = @qry + 'WHERE CAMPUS = ' + @id

    EXEC(@qry)

END

Posted 19-Feb-13 7:16am

Maciej Los