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.
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 Answers
Answers 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.
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
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()) Answers 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.
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:
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
unpivotdata must be cast to its largest type (usually, string) - unnecessary operations are performed (
pivot->unpivot) instead of just rendering the data; - it does not working well with large amount of data (it is slow)
and others..
Answers 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
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 Answers 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
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
Answers 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 Answers 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() 
0 comments:
Post a Comment