在SQL Server中使用IF语句的表值函数

Question

我有一个学生表，包含以下参数：

[ID] [nvarchar](50) NOT NULL,
[Firsname] [nvarchar](50) NOT NULL,
[Lastname] [nvarchar](50) NOT NULL,
[Melicode] [nchar](10) NOT NULL,
[City] [nvarchar](50) NOT NULL,
[Province] [nvarchar](50) NOT NULL,
[Active] [int] NULL

我想编写一个名为Show的表值函数，它有一个数字参数。函数将按以下方式运行：

• 如果@number = 1，则返回从Student表中的所有列
• 如果@number = 2，则仅返回Student表中的City列
• 如果@number = 3，则仅返回Student表中的Province列

我写了以下T-SQL，但仅适用于（如果（@number = 1））。当用户输入@number为2或3时，该函数无法工作。谢谢

 Create function Show(@number int)
RETURNS @result TABLE
(
    [ID] [nvarchar](50) NOT NULL,
    [Firsname] [nvarchar](50) NOT NULL,
    [Lastname] [nvarchar](50) NOT NULL,
    [Melicode] [nchar](10) NOT NULL,
    [City] [nvarchar](50) NOT NULL,
    [Province] [nvarchar](50) NOT NULL,
    [Active] [int] NULL
) 
AS
BEGIN

    IF  (@number = 1)
         INSERT INTO @result SELECT * from Student 

    IF (@number = 2)
         INSERT INTO @result (City) values ((SELECT City from Student))

     IF (@number = 3)
         INSERT INTO @result (Province) values ((SELECT Province from Student))

    RETURN -- @Players (variable only required for Scalar functions)

END

go
select *from dbo.show(1)

- Ramon Fonis

---

不能工作？难以置信。 - Giorgi Nakeuri

Answer 1

这样不会奏效：

INSERT INTO @result (City) 
VALUES ((SELECT City from Student))

要么你有所有的值作为标量 SQL 变量或字面量 - 那么你可以使用

INSERT INTO @result (City) 
VALUES ('New York')

INSERT INTO @result (City) 
VALUES (@ChosenCity)

或者如果您有一个SELECT语句需要填充这些值-那么您需要使用此语法：

INSERT INTO @result (City) 
    SELECT City 
    FROM Student

没有 VALUES 关键字。正如 @GiorgiNakeuri 正确地指出的那样 - 这将导致插入失败，因为所有列都需要一个值（具有 NOT NULL 属性），因此这个插入操作无法成功 - 您需要提供 所有 NOT NULL 值（或为每个列定义默认值）

- marc_s

---

你忘记了所有的列都是非空的。 - Giorgi Nakeuri

@marc_s 我想让ID不可为空。在我的数据库中，将其设置为可为空似乎不合逻辑，但如果不这样做，函数就无法正常工作。我不知道该如何解决它。 - Ramon Fonis

您IP地址为143.198.54.68，由于运营成本限制，当前对于免费用户的使用频率限制为每个IP每72小时10次对话，如需解除限制，请点击左下角设置图标按钮（手机用户先点击左上角菜单按钮）。 - marc_s

Answer 2

您提到@result具有所有NOT NULL列。如果您只想将城市插入@result，则其余列将被视为Null，因此会出现错误。您没有提及@result列是NOT NULL列，还有一个是。从INSERT语句中删除VALUES关键字，因为它正在使用Select语句进行插入。

- Aj.na

---

是的，它有帮助。但问题在于结果提供了一个表格，其中只有一列具有数据，所有其他列都具有NULL值，这看起来不太好。 - Ramon Fonis

@user3187561，您想用其他值替换这些空值吗？ - Aj.na

不，我只想在结果中隐藏NULL值。例如，如果我从用户读取@number = 2，则结果应该只是具有值的City列，而不是其他带有NULL的列。 - Ramon Fonis

Answer 3

CREATE FUNCTION dbo.Show
(
    @number INT
)
RETURNS @result TABLE
(
    ID NVARCHAR(50),
    Firsname NVARCHAR(50),
    Lastname NVARCHAR(50),
    Melicode NCHAR(10),
    City NVARCHAR(50),
    Province NVARCHAR(50),
    Active INT
)
AS
BEGIN

    IF (@number = 1)
        INSERT INTO @result
        SELECT * FROM dbo.Student

    IF (@number = 2)
        INSERT INTO @result (City)
        SELECT City FROM dbo.Student

    IF (@number = 3)
        INSERT INTO @result (Province)
        SELECT Province FROM dbo.Student

    RETURN

END
GO

SELECT * FROM dbo.Show(2)

- Devart

---

@Giorgi Nakeuri，也许你想说一下这个答案有什么问题？缺少了两个单词？ :) - Devart

是的，缺少了两个单词。而且它不能工作，因为表中的所有列都不可为空，但没人注意到这一点。 - Giorgi Nakeuri

@Giorgi Nakeuri - 谢谢，非空值是另一个问题。我希望ID不可为空。但是该函数无法使用。 - Ramon Fonis

Answer 4

返回的表格取决于结果表格的声明方式。以下查询（在某种程度上）有效，但结果包括所有未被@number参数所针对的列中的NULL值：

CREATE TABLE dbo.z_Show (str1 VARCHAR(10), str2 VARCHAR(10), str3 VARCHAR(10))

INSERT z_show
SELECT 1, 1, 1 UNION ALL
SELECT 2, 2, 2 UNION ALL
SELECT 3, 3, 3

CREATE FUNCTION dbo.Show(@number int)
RETURNS @result TABLE
(
    --[ID] [nvarchar](50) NOT NULL,
    --[Firsname] [nvarchar](50) NOT NULL,
    --[Lastname] [nvarchar](50) NOT NULL,
    --[Melicode] [nchar](10) NOT NULL,
    --[City] [nvarchar](50) NOT NULL,
    --[Province] [nvarchar](50) NOT NULL,
    --[Active] [int] NULL
    str1 VARCHAR(10), str2 VARCHAR(10), str3 VARCHAR(10)
) 
AS
BEGIN
--for debugging|start
--DECLARE @number INT = 3
--DECLARE @result TABLE (str1 VARCHAR(10), str2 VARCHAR(10), str3 VARCHAR(10))
--for debugging|end

    IF  (@number = 1)
    BEGIN
           --PRINT ('IF (@number = 1)')
         INSERT INTO @result SELECT * from dbo.z_Show
    END

    IF (@number = 2)
    BEGIN
        --PRINT ('IF (@number = 2)')
         INSERT INTO @result (str2) SELECT str2 from dbo.z_Show
    END

     IF (@number = 3)
     BEGIN
           --PRINT ('IF (@number = 3)')
         INSERT INTO @result (str3) SELECT str3 from dbo.z_Show
     END

    RETURN -- @Players (variable only required for Scalar functions)
END

SELECT 'number 1 was passed', *
FROM dbo.show(1)

SELECT 'number 2 was passed', *
FROM dbo.show(2)

SELECT 'number 3 was passed', *
FROM dbo.show(3)

- t1t1an0

Answer 5

第二和第三个案例的插入语句是不正确的。在插入来自选择语句的值时，不需要使用VALUES关键字。

- Eduard Uta

---

我删除了VALUES关键字。所以它变成了这样：IF (@number = 2) INSERT INTO @result (City) (SELECT City from Student) 但是，仍然不起作用。 - Ramon Fonis

当然，您不需要（和）括号 - 这些也应该被删除。 - Eduard Uta