Create Store Procedure with an input parameters from a report and create excel file with the result set

Question

So I want to build a store procedure with input parameters, the user will enter the input values using a reporting tool(Microstrategy) then I will build that logic to extract that to an excel or flat file to a destination folder for the user. I'm going to use SSIS to create the file or I can do that in the store procedure if so how will I do that and also I want to have something like a message box that will say your file is now ready in the destination folder. Below is the input store procedure I came up with:

create PROCEDURE dbo.uspGetReportData
@Startdate Date = NULL,
@Enddate DATE = NULL,
@FileID BIGINT = NULL,
@ClientName VARCHAR(50) = NULL

AS
BEGIN
SET NOCOUNT ON;
SELECT
o.FileID,
dl.AddressLine1 AS [PropertyAddress],
dl.City,
dl.[State],
dl.Zip,
c.ClientName,
o. OrderDate

FROM [dbo].[Orders] o
LEFT JOIN dbo.OrderLoan dl ON dl.OrderLoanID = o.OrderLoanID
LEFT JOIN dbo.Client c ON c.ClientID = o.ClientID

WHERE orderDate >= ISNULL(@Startdate,orderDate)
AND orderDate <= ISNULL(@Enddate,orderDate)
AND FileID IN( ISNULL(@FileID,FileID))
AND c.ClientName IN ( ISNULL(@ClientName,ClientName))

END

Answer 1

WHERE orderDate >= ISNULL(@Startdate,orderDate)
AND orderDate <= ISNULL(@Enddate,orderDate)
AND FileID IN( ISNULL(@FileID,FileID))
AND c.ClientName IN ( ISNULL(@ClientName,ClientName))

Watch out! If any of these columns are nullable, this may not work as you presumably intend. Better write this as:

WHERE (orderDate >= @Startdate OR @Startdate IS NULL)
 AND (orderDate <= @Enddate OR @Enddate IS NULL)
 AND (FileID = @FileID OR @FileID IS NULL)
 AND (c.ClientName = @ClientName OR ClientName IS NULL)

I also replace the IN operators with = to make the code more straightforward.

As for creating the Excel file, don't do that from the stored procedure. Your DBA may not agree to change that configuration parameter that Melissa saw you. Furthermore, I have seen so many posts over the years where people have tried it, and it has failed with incomprehensible error messages.

Answer 2

Hi @pizaro ，

Thanks for your update.

I am not an expert in SSIS, you could refer Execute SQL Task in SSIS: Output Parameters vs Result Sets and check whether it is helpful to you.

In addition, you could consider whether it is possible to use BCP. You could refer
Creating CSV Files Using BCP and Stored Procedures

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.