Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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:
 
select code,name from table1 where campus=(//here i want to implement the logic)
Posted 19-Feb-13 1:14am
josh-jw10.7K
Edited 19-Feb-13 1:15am
v3
Comments
Ankur\m/ at 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 at 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/ at 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/ at 19-Feb-13 7:46am
   
Are you using a stored procedure to execute this query?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Better you go for dynamic sql.Suppose you have a department table which has 2 columns(Id, Name)
The code is as follows
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;
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Create a stored procedure, like this:
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
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

Hi ,
 
Try like this
 
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
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 275
1 Maciej Los 230
2 Sergey Alexandrovich Kryukov 185
3 Afzaal Ahmad Zeeshan 152
4 ProgramFOX 130
0 OriginalGriff 6,524
1 Sergey Alexandrovich Kryukov 6,048
2 DamithSL 5,218
3 Manas Bhardwaj 4,657
4 Maciej Los 4,150


Advertise | Privacy | Mobile
Web03 | 2.8.1411022.1 | Last Updated 20 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100