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

I have a Student table consists of following parameters

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

[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

i want to write a Table-Valued Function named Show which has one parameter as number. the function will act as following

我想写一个表值函数Show，它的一个参数是number。函数的作用如下

if @number = 1 , returns all columns from Student table
如果@number = 1，返回学生表中的所有列
if @number = 2 , returns only City from Student
如果@number = 2，则只从学生返回城市
if @number = 3 , returns only Province from Student
如果@number = 3，则只从学生返回省份

i wrote the following T-SQL, but it only works for (if (@number = 1)). When the user enter @number as 2 or 3, the function does not work. Thank You

我编写了下面的T-SQL，但是它只适用于(if (@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)

5 个解决方案

#1

This is not going to work:

这是行不通的:

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

Either you have all the values as scalar SQL variables, or literals - then you can use

您可以将所有的值作为标量SQL变量，也可以使用常量

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

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

or you have a SELECT statement to fill the values - then you need this syntax:

或者你有一个SELECT语句来填充这些值——然后你需要这个语法:

INSERT INTO @result (City) 
    SELECT City 
    FROM Student

without the VALUES keyword. And as @GiorgiNakeuri correctly states - this will then fail because all your columns require a value (have the NOT NULL attribute), so this insert cannot succeed - you need to provide all NOT NULL values (or define a default value for each column)

没有关键字的值。@GiorgiNakeuri正确地声明—这将失败，因为您的所有列都需要一个值(具有NOT NULL属性)，因此这个插入不能成功—您需要提供所有非空值(或为每个列定义一个默认值)

#2

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)

#3

the table returned is dictated by how the result table was declared. the query below works (in a sense) but the results include all the columns with NULLs for those columns not targeted by the @number parameter:

返回的表由如何声明结果表决定。下面的查询(在某种意义上)是有效的，但是结果包括了@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)

#4

You mentioned @result has all NOT NULL columns. If you want to insert only city into that @result, it will take remaining columns as Null so that's why an error happened. You don't mention that @result columns are NOT NULL columns and one more is. Remove VALUES keyword from the INSERT statement because it is inserting with a Select statement

您提到的@result具有所有非空列。如果您想要将city插入到@result中，那么它将保留其余的列为Null，这就是错误发生的原因。您没有提到@result列不是空列，还有一个是空列。从INSERT语句中删除VALUES关键字，因为它使用Select语句进行插入

#5

The insert statements for cases 2 and 3 are incorrect. No need for VALUES keyword when inserting values coming from a select statement.

第二和第三种情况的插入语句是不正确的。当插入来自select语句的值时，不需要使用值关键字。

#1