从sql server中的特定列中选择值？

问题描述：

存储过程： -从sql server中的特定列中选择值？

CREATE PROCEDURE [dbo].[RT_SelectAll_BatchHistory_By_SchedulerTime] 
@Skip INT, 
@Take INT, 
@ResultCode AS INT = 0 OUTPUT, 
@ResultText AS VARCHAR(50)=NULL OUTPUT 
AS 
BEGIN 
SET NOCOUNT ON; 
BEGIN TRY 
SELECT 
     [SchedulerTime] 
     ,[ExecutedOn] 
     ,[FinishedOn] 
FROM [T_Scheduler] WITH(NOLOCK) 
WHERE [IsActive] = 1 
ORDER BY [SchedulerID] 
OFFSET @Skip ROWS 
FETCH NEXT @Take ROWS ONLY 
SET @ResultCode = 0 
SET @ResultText='SUCCESS' 
END TRY 
BEGIN CATCH 
SET @ResultCode = -1 
SET @ResultText='ERROR' 
END CATCH 
END

在这个存储过程要选择所有的SchedulerTime列行和空值从executedOn和finishedOn。

怎么能达到这个条件？

答

SELECT 
     [SchedulerTime] 
     ,[ExecutedOn] 
     ,[FinishedOn] 
FROM [T_Scheduler] WITH(NOLOCK) 
WHERE [IsActive] = 1 AND [ExecutedOn] IS NULL AND [FinishedOn] IS NULL

这工作得很好对我来说

答

考虑到你都知道脏的读取你的代码，你可以尝试这样的事情..

SELECT 
     [SchedulerTime] 
     ,[ExecutedOn] = NULL 
     ,[FinishedOn] = NULL 
FROM [T_Scheduler] WITH(NOLOCK) 
WHERE [IsActive] = 1 
ORDER BY [SchedulerID]

答

使用NULLIF在你的SELECT查询

SELECT 
     [SchedulerTime] 
     ,NULLIF([ExecutedOn],[ExecutedOn]) AS [ExecutedOn] 
     ,NULLIF([FinishedOn],[FinishedOn]) AS [FinishedOn] 
FROM [T_Scheduler] WITH(NOLOCK) 
WHERE [IsActive] = 1 
ORDER BY [SchedulerID] 
-----------------------------------------------  
SELECT 
     [SchedulerTime] 
     ,NULL AS [ExecutedOn] 
     ,NULL AS [FinishedOn] 
FROM [T_Scheduler] WITH(NOLOCK) 
WHERE [IsActive] = 1 
ORDER BY [SchedulerID]

从sql server中的特定列中选择值？

相关推荐