具有多个查询和不同选定列的存储函数

I have series of queries based on a report type. For simplicity here is an example of what i'm trying to do:

我有一系列基于报告类型的查询。为简单起见，这是我正在尝试做的一个例子：

If @Reporttype = '1'
Select lcustomerid, lname, fname
from customers
Where dtcreated > @startdate

Else if @Reporttype = '2'
Select barcode, lname, fname 
from employees
where dtcreated > @startdate

Else if @reporttype = '3'
Select thetime, lname, name, barcode, lcustomerid
from Customers
where dtcreated > @startdate

You'll notice that I run 3 separate queries, based on the report type being passed. You'll also notice I am returning different columns and the number of columns.

根据传递的报告类型，您会注意到我运行了3个单独的查询。您还会注意到我返回的是不同的列和列数。

I'd like to make this a stored function, and return the columns I need based on the report type I pass. However, I know that since the number of columns, and the column names are different - that's not going to work as a stored function as I'd like it to.

我想把它作为一个存储函数，并根据我传递的报告类型返回我需要的列。但是，我知道由于列数和列名不同 - 这不会像我希望的那样作为存储函数工作。

The major problem here will be reporting this information - I don't want to have separate functions, because i'll have to maintain different reports for each report type.

这里的主要问题是报告此信息 - 我不希望有单独的功能，因为我必须为每种报告类型维护不同的报告。

Is there a way I can make this work?

我有办法让这项工作成功吗？

6 个解决方案

#1

You can use multi-statement function but you need to specify all columns which will be returned by 3 select statements. It seems it's impossible return multiple result sets.

您可以使用多语句函数，但需要指定将由3个select语句返回的所有列。似乎不可能返回多个结果集。

User-defined functions can not return multiple result sets. Use a stored procedure if you need to return multiple result sets. https://msdn.microsoft.com/en-us/library/ms191320.aspx

用户定义的函数不能返回多个结果集。如果需要返回多个结果集，请使用存储过程。 https://msdn.microsoft.com/en-us/library/ms191320.aspx

This is one inconvenience but in report you can use only columns you need, others will be nulls.

这是一个不便，但在报告中，您只能使用您需要的列，其他将为空。

CREATE FUNCTION MyFun
(
    @Reporttype int,
    @startdate datetime
)
RETURNS 
@Result TABLE 
(
    lcustomerid int, 
    lname nvarchar(50),
    fname nvarchar(50),
    barcode int,
    thetime datetime,
    name nvarchar(50)
)
AS
BEGIN
    If @Reporttype = '1'
        insert into @Result (lcustomerid, lname, fname)
        select lcustomerid, lname, fname
        from customers
        Where dtcreated > @startdate

    Else if @Reporttype = '2'
        insert into @Result (barcode, lname, fname)
        Select barcode, lname, fname 
        from employees
        where dtcreated > @startdate

    Else if @reporttype = '3'
        insert into @Result (thetime, lname, name, barcode, lcustomerid)
        Select thetime, lname, name, barcode, lcustomerid
        from customers
        where dtcreated > @startdate

    RETURN 
END

So, you can call function in this way

所以，你可以用这种方式调用函数

SELECT * FROM dbo.MyFun (1, getdate())

#2

If you cannot use stored procedure and you need to use a function, you can UNPIVOT the data and than in the client side you can PIVOT it.

如果您不能使用存储过程并且需要使用某个函数，则可以对数据进行UNPIVOT，而在客户端则可以对其进行PIVOT。

I need to do something like this when different number of columns are returned to SQL Server Reporting Services report. For example, the following code is always returning three columns - RowID, Column, Value:

当不同数量的列返回到SQL Server Reporting Services报告时，我需要执行类似的操作。例如，以下代码始终返回三列 - RowID，Column，Value：

DECLARE @Table01 TABLE
(
    [ID] INT
   ,[Value01] INT
   ,[Value02] NVARCHAR(256)
   ,[Value03] SMALLINT
);

DECLARE @Table02 TABLE
(
    [ID] INT
   ,[Value01] INT
);

INSERT INTO @Table01 ([ID], [Value01], [Value02], [Value03])
VALUES (1, 111, '1V2', 7)
      ,(2, 222, '2V2', 8)
      ,(3, 333, '3V2', 9);

INSERT INTO @Table02 ([ID], [Value01])
VALUES (1, 111)
      ,(2, 222)
      ,(3, 333);


-- your function starts here

DECLARE @Mode SYSNAME = 'Table01' -- try with 'Table02', too

DECLARE @ResultSet TABLE
(
    [RowID] INT
   ,[Column] SYSNAME
   ,[Value] NVARCHAR(128)
);

IF @Mode = 'Table01'
    BEGIN;
        INSERT INTO @ResultSet ([RowID], [Column], [Value])
        SELECT [ID]
              ,[Column]
              ,[Value]
        FROM
        (
            SELECT [ID]
                  ,CAST([Value01] AS NVARCHAR(256))
                  ,CAST([Value02] AS NVARCHAR(256))
                  ,CAST([Value03] AS NVARCHAR(256))
            FROM @Table01
        ) DS ([ID], [Value01], [Value02], [Value03])
        UNPIVOT
        (
            [Value] FOR [Column] IN ([Value01], [Value02], [Value03])
        ) UNPVT
    END;
ELSE
    BEGIN;
        INSERT INTO @ResultSet ([RowID], [Column], [Value])
        SELECT [ID]
              ,[Column]
              ,[Value]
        FROM
        (
            SELECT [ID]
                  ,CAST([Value01] AS NVARCHAR(256))
            FROM @Table02
        ) DS ([ID], [Value01])
        UNPIVOT
        (
            [Value] FOR [Column] IN ([Value01])
        ) UNPVT
    END;

SELECT *
FROM @ResultSet;

Then in the reporting I need to perform pivot operation again. This is workaround with many limitations:

然后在报告中我需要再次执行枢轴操作。这是一种解决方法，有许多限制：

• the unpivot data must be cast to its largest type (usually, string)
• 必须将unpivot数据强制转换为其最大类型（通常为字符串）
• unnecessary operations are performed (pivot -> unpivot) instead of just rendering the data;
• 执行不必要的操作（pivot - > unpivot）而不是仅渲染数据;
• it does not working well with large amount of data (it is slow)
• 它不适用于大量数据（速度很慢）

and others..

和别的..

#3

For This you may create a scalar value function that return an xml type column and then you can populate that xml tag values to your report screen

为此，您可以创建一个返回xml类型列的标量值函数，然后您可以将该xml标记值填充到报表屏幕

CREATE FUNCTION ReportFunc
(
    @intReporttype int,
    @dtStartdate datetime
)
RETURNS XML
BEGIN

Declare @xmlResult xml 

    If @intReporttype = '1'
        SET @xmlResult = (
                select lcustomerid, lname, fname
                from customers
                Where dtcreated > @dtStartdate
                FOR XML PATH (''), TYPE  
                )

    Else if @intReporttype = '2'
        SET @xmlResult = (
                    Select barcode, lname, fname 
                    from employees
                    where dtcreated > @dtStartdate
                    FOR XML PATH (''), TYPE  
                    )

    Else if @intReporttype = '3'
        SET @xmlResult = (  
                    Select thetime, lname, name, barcode, lcustomerid
                    from customers
                    where dtcreated > @dtStartdate
                    FOR XML PATH (''), TYPE  
                    )

    RETURN @xmlResult
END

#4

In SQL it is difficult to create something similar so generic or abstract, especially when it has to do with SELECT of colums. If your purpose is to write as less code as you can in order your sql script to be maintained easily and to be able to add new report types in the future with just minor changes I would suggest to use a stored procedure with dynamic sql. You cannot use a function while you wisk your SELECT to be dynamic, its the wrong method. I would write something like that

在SQL中，很难创建类似于通用或抽象的东西，特别是当它与SELECT of colums有关时。如果您的目的是编写尽可能少的代码，以便轻松维护sql脚本，并且能够在将来添加新的报表类型，只需进行少量更改，我建议使用带有动态sql的存储过程。当您将SELECT视为动态时，您不能使用函数，这是错误的方法。我会写那样的东西

CREATE PROCEDURE MyProcedure
(
@ReportType int,
@startdate datetime
)
AS

BEGIN
DECLARE @colNames varchar(MAX),@tblName varchar(MAX),@sSQL varchar(MAX);
SELECT @colNames = CASE 
                    WHEN @ReportType = 1 THEN
                     'lcustomerid, lname, fname' --You can also add alias
                     WHEN @ReportType = 2 THEN
                     'barcode, lname, fname'
                     WHEN @ReportType = 3 THEN
                     'thetime, lname, name, barcode, lcustomerid'
                     ELSE
                     RAISEERROR('Error msg');
                    END,
        @tblName = CASE 
                    WHEN @ReportType = 1 OR @ReportType = 3 THEN
                     'customers' --You can also add alias
                    WHEN @ReportType = 2 THEN
                     'employees'
                     ELSE
                     RAISEERROR('Error msg');
                    END

SET @sSQL = 
'Select '+@colNames+'
from '+@tblName +'
where dtcreated > '''+CONVERT(varchar(10), @startdate, 121)+''''

EXEC(@sSQL)
END

And you will call it as

你会称之为

EXEC MyProcedure 1,'20170131' 

for example With this code every time you want a new report type you will need to add just another line in case with the requested column names. I have used this way in working with Crystal reports and I think it is the best possible solution

例如，每当您需要新的报告类型时，使用此代码，您将需要添加另一行，以防请求的列名称。我使用这种方式处理Crystal报表，我认为这是最好的解决方案

#5

If you can use Stored Procedures then for maintainability I would look at using a master stored procedure which calls other stored procedures to return different result sets:

如果你可以使用存储过程然后为了可维护性，我会看一下使用一个调用其他存储过程的主存储过程来返回不同的结果集：

CREATE PROCEDURE MyProc_1(@startdate DateTime)
AS
BEGIN
    SELECT lcustomerid, lname, fname 
    FROM customers WHERE dtcreated > @startdate 

END
GO
CREATE PROCEDURE MyProc_2(@startdate DateTime)
AS
BEGIN
    SELECT barcode, lname, fname 
    FROM employees where dtcreated > @startdate 
END
GO
CREATE PROCEDURE MyProc_3(@startdate DateTime)
AS
BEGIN
    SELECT thetime, lname, name, barcode, lcustomerid 
    FROM Customers WHERE dtcreated > @startdate
END
GO

CREATE PROCEDURE MyProc(@Reporttype char(1), @startdate DateTime)
AS 
BEGIN
    IF @Reporttype = '1' EXEC MyProc_1 @startdate
    ELSE IF @Reporttype = '2'  EXEC MyProc_2 @startdate
    ELSE IF @reporttype = '3'  EXEC MyProc_3 @startdate
END
GO

And to use:

并使用：

DECLARE @dt datetime = getdate()
EXEC MyProc 1, @dt

#6

CREATE Proc Emp_det 
(
@Reporttype INT,
@startdate DATETIME
)
AS
BEGIN
    If @Reporttype = '1' BEGIN

        Select lcustomerid, lname, fname
        FROM customers
        WHERE dtcreated > @startdate

    END 
    ELSE IF @Reporttype = '2' BEGIN

        Select barcode, lname, fname 
        FROM employees
        WHERE dtcreated > @startdate
    END
    ELSE IF @reporttype = '3' BEGIN
        Select thetime, lname, name, barcode, lcustomerid
        FROM Customers
        WHERE dtcreated > @startdate    

    END
END
GO

Exec Emp_det 1,GETDATE()