创建存储过程时如何在无参数情况下为指定列应用条件函数输出结果？

Got it, let's sort this out for you! The problem with your current code is that you're using standalone IF statements outside your SELECT clause—those won't generate dynamic columns for each row in your result set. Instead, we need to use CASE expressions directly inside the SELECT to calculate those conditional values per row, which is exactly what you need here (no parameters required).

Let's break down the requirements and fix each part: #

• PayComment Column Logic: Use a nested CASE to handle three conditions in order: check if COLA = 'yes' first, then verify if HireDate matches StartDate, and fall back to "Pay raise" if neither condition is met.
• TermDate Column Logic: Use a CASE to check for NULL values: return "Still Employed" if TermDate is null, otherwise convert the date to your desired format (style 1, which outputs mm/dd/yy).

Here's the corrected stored procedure code: #

USE PR;
GO
CREATE PROC spPayIncreaseListing
AS
BEGIN
    SELECT 
        FirstName,
        LastName,
        CONVERT(varchar, EmpData.HireDate, 1) AS HireDate, -- Use actual table column, not current date
        PayRate,
        CONVERT(varchar, TempWork.StartDate, 1) AS PayRateStartDate, -- Map to your table's StartDate
        CONVERT(varchar, TempWork.PayRateEndDate, 1) AS PayRateEndDate, -- Use actual end date column
        -- Conditional PayComment calculation
        CASE
            WHEN COLA = 'yes' THEN 'Cost of living increase'
            WHEN EmpData.HireDate = TempWork.StartDate THEN 'Initial Payrate'
            ELSE 'Pay raise'
        END AS PayComment,
        -- Conditional TermDate handling
        CASE
            WHEN TermDate IS NULL THEN 'Still Employed'
            ELSE CONVERT(varchar, TermDate, 1)
        END AS TermDate
    FROM EmpData 
    JOIN TempWork ON EmpData.EmpID = TempWork.EmpID
    ORDER BY LastName, WorkID;
END;
GO

Key fixes and notes: #

• Replaced standalone IF statements with CASE expressions inside the SELECT—this is the standard way to create row-level conditional columns in SQL.
• Swapped getdate() references with actual table columns (your original code was using the current date instead of stored row values).
• Fixed the TermDate = NULL check to use TermDate IS NULL—SQL doesn't recognize = for comparing null values, you must use IS NULL/IS NOT NULL.
• Added BEGIN/END blocks around the procedure body for clarity, which is a good practice for SQL Server stored procedures.

This will return all your desired columns, with the conditional logic applied correctly to every row in your result set.

内容的提问来源于stack exchange，提问作者rylew0925