Pass and retrieve data to and from a Stored Procedure

[Halliarse]

I have a stored procedure that I want to pass a Crystal parameter, SalonID into @SalonID, as well as the result of a formula into @Inputdate. The stored procedure creates 12 rows of data, which I then need to pass the availableDays back into the Crystal report and to be honest, I have no idea where to start! Could someone please point me in the right direction? The stored procedure is as below!

Mant thanks

Steve

USE [Snip-IT]
GO
/****** Object: StoredProcedure [dbo].[GetMonthlyAvail] Script Date: 05/11/2015 14:03:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetMonthlyAvail]
@SalonID int,
@InputDate date
AS
BEGIN
SET NOCOUNT ON;

; With Calendar As
(
Select DateAdd(Day, Number, @InputDate) As WorkDay,
DatePart(Weekday, DateAdd(Day, Number, @InputDate)) As DayId
From Master..spt_values
Where Type = 'P'
And number < 366
)
Select Top 12
Year(Calendar.WorkDay) As WorkYear,
Month(Calendar.Workday) As WorkMonth,
Count(*) As WorkingDays,
Count(BankHoliday) As BankHolidays,
Count(*) - Count(BankHoliday) As AvailableDays
From Calendar
Inner Join salonOpening
On Calendar.DayId = salonOpening.Day
Left Join PublicHolidays
On Calendar.Workday = PublicHolidays.BankHoliday
where SalonOpening.SalonID = @SalonID
Group By Year(Calendar.WorkDay), Month(Calendar.Workday)
Order By WorkYear, WorkMonth
END

 

[hilfy]

You can connect Crystal directly to a stored procedure. It looks like you're using SQL Server and the result of your stored proc is a table, which should work as a datasource in Crystal with no problems. It would be easy to send the SalonID parameter in, but the input date is going to be more problematic.

What is the formula for InputDate? If you calculate that date in the stored proc, then you don't need to pass a parameter in. If you can't update this stored proc, I would create a new one where you calculate it and then call Select * from the existing one.

When you use a stored proc, you have to let Crystal create the parameter for you - stored procs cannot "see" parameters created in the Field Explorer, but the parameter created when adding the view will be visible in the Field Explorer so that you can edit it if you need to.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Halliarse]

The formula for InputDate is a sfollows:

select month(CurrentDate)
case 1:
cdate("01/02/" & replace(replace(totext(year(CurrentDate) - 1),",",""),".00",""))
case 2:
cdate("01/03/" & replace(replace(totext(year(CurrentDate) - 1),",",""),".00",""))
case 3:
cdate("01/04/" & replace(replace(totext(year(CurrentDate) - 1),",",""),".00",""))
case 4:
cdate("01/05/" & replace(replace(totext(year(CurrentDate) - 1),",",""),".00",""))
case 5:
cdate("01/06/" & replace(replace(totext(year(CurrentDate) - 1),",",""),".00",""))
case 6:
cdate("01/07/" & replace(replace(totext(year(CurrentDate) - 1),",",""),".00",""))
case 7:
cdate("01/08/" & replace(replace(totext(year(CurrentDate) - 1),",",""),".00",""))
case 8:
cdate("01/09/" & replace(replace(totext(year(CurrentDate) - 1),",",""),".00",""))
case 9:
cdate("01/10/" & replace(replace(totext(year(CurrentDate) - 1),",",""),".00",""))
case 10:
cdate("01/11/" & replace(replace(totext(year(CurrentDate) - 1),",",""),".00",""))
case 11:
cdate("01/12/" & replace(replace(totext(year(CurrentDate) - 1),",",""),".00",""))
case 12:
cdate("01/01/" & replace(replace(totext(year(CurrentDate) - 1),",",""),".00",""))

The report uses this anyway, however I think it could easily be included in the Stored Procedure.

You are spot on with the output of the Stored Procedure, but I still don't understand how I retrieve the output data into the Crystal Report...could you provide an example please?

 

[hilfy]

BTW, here's an easier way to do your ToText without the Replace:

ToText(year(CurrentDate) - 1, '', 0) - this will take out the command and the decimal places.

In the Database Expert, when you connect to the database you should have a "Stored Procedures" folder available inside the connection. Open that and then select the stored proc you want to use. Crystal will create parameters based on the ones in the stored proc.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Halliarse]

Hi Hilfy

Apologies for the ridiculous period to reply, I've been sidetracked on another project but now I'm back on this one!!

I have linked the stored procedure to the report but this now gives me 2 starting points!

I've thought of possibly an easier approach but not entirely sure about how to go about it!

If I still call the stored procedure, include the code for the date, therefore passing no parameters, but instead, write the TOP 12 entries to a table, I could add the table to the Crystal report and then just retrieve the 12 columns of data from there!

What do you think? Could you offer advice as to how to go about it?

Thanks

Steve

 

[hilfy]

It can be a challenge connecting Crystal to a temp table where the table name changes every time you run the report. But, you could return a cursor from the stored proc that contains your twelve columns and use that in the report with no problems.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Halliarse]

I have no idea about using cursors....I thought I would just manually create a 2 column table, id, value and then truncate it everytime the stored procedure is called

 

[hilfy]

To get that to work, the stored procedure would have to be called from outside of Crystal and then your report would connect directly to the table. You would probably not be able to call the stored proc from Crystal.

What type of database are you connecting to? I may be able to point you to information about how to build a cursor from there.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Halliarse]

I'm using SQL Server 2008 R2 and I've created the table slightly different in that it will only ever hold 1 record.....12 columns of data

 

[hilfy]

I've worked with stored procs for Crystal in Oracle, but not in SQL Server. However, after a bit of research, here's what I would try:

1. Keep your current logic in the stored proc for adding data to the table.

2. At the end of the SP, add "Select * from <your new table>".

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Halliarse]

Thanks, I'll let you know how it goes later on!

 

[Halliarse]

Hi Hilfy

I have made some decent progress but I'm struggling again now!!

I have created a table called AvailableDays which will hold records keyed on SalonID with 12 values, Month1, Month2.....Month12

I have create a new stored procedure called CalculateInputDate which I now execute from my original GetMonthlyAvail stored procedure which I have tested and works fine. What I need to be able to do now is to update 12 values for AvailableDays into the AvailableDays table....can you advise please? My GwtMonthlyAvail stored proecedure is as below:

USE [Snip-IT]
GO
/****** Object: StoredProcedure [dbo].[GetMonthlyAvail] Script Date: 06/02/2015 13:36:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetMonthlyAvail]
@SalonID int
AS
BEGIN

Declare @InputDate Date
EXEC [CalculateInputDate] @InputDate OUTPUT
Select @InputDate as 'ResultFromNestedSP'

SET NOCOUNT ON;

; With Calendar As
(
Select DateAdd(Day, Number, @InputDate) As WorkDay,
DatePart(Weekday, DateAdd(Day, Number, @InputDate)) As DayId
From Master..spt_values
Where Type = 'P'
And number < 366
)
Select Top 12
Year(Calendar.WorkDay) As WorkYear,
Month(Calendar.Workday) As WorkMonth,
Count(*) As WorkingDays,
Count(BankHoliday) As BankHolidays,
Count(*) - Count(BankHoliday) As AvailableDays
From Calendar
Inner Join salonOpening
On Calendar.DayId = salonOpening.Day
Left Join PublicHolidays
On Calendar.Workday = PublicHolidays.BankHoliday
where SalonOpening.SalonID = @SalonID
Group By Year(Calendar.WorkDay), Month(Calendar.Workday)
Order By WorkYear, WorkMonth
END

Many thanks again

Steve