sql 2005中的case语句 [英] case statement in sql 2005

问题描述

在我的查询条件中我正在过滤一个列名校园。如果校园是5，那么选择校园为5的记录，否则选择所有记录。如何写查询？



查询如：

 选择代码，名称来自 table1 其中 campus =（//这里我想 实现逻辑）

解决方案

更好的去动态sql.Suppose你有一个有2列的部门表（Id，Name）

代码如下

 声明 < span class =code-sdkkeyword> @ Id   int  =  3 ; 
 声明  @sql   nvarchar  （max）= '  SELECT Id，Name FROM Departments'; 
 
  IF   @ Id  =  2  
  SET   @sql  + = '  WHERE Id =' + cast（ @ id   as   varchar （ 10 ））; 
 
  EXEC  sp_ExecuteSQL  @ 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 ）
 
 结束 

您好，

您也可以尝试这种风格：

 DECLARE @campus INT =（
 SELECT campus 
 FROM table1 
 WHERE campus = 5 
）
 
 SELECT code， 
 NAME 
 FROM table1 
 WHERE campus = COALESCE（@campusId，''''）=''''
 OR campus = @campus 

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)

解决方案

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;

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

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

这篇关于sql 2005中的case语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！