Stored Procedure to a View??

[tgtfranz]

This may be a crazy question, but I have been beating it around in my head for days as to how I would possibly do this.

Is there anyway to convert this into a view with a given date range that will change on execution of form?

Maybe there is a way that we can get this into a view, but I am not sure how to get my date range without parameters.

CREATE PROCEDURE dbo.sp_PayTypesandHoursSummaryDateRange

@EnterEmpNo int
,@EnterStartingCheckDate datetime
,@EnterEndingCheckDate datetime
AS

SELECT DISTINCT
pc.emp_no
, pcpa.pay_type
, pt.description
, SUM(pcpa.hours) AS Hours
, SUM(pcpa.cmw_gross) AS Gross

FROM
pay_checks AS pc
INNER JOIN
pay_checks_pay_assoc AS pcpa
ON
pc.pay_checks = pcpa.pay_checks
INNER JOIN
dbo.pay_types AS pt
ON
pcpa.pay_type = pt.pay_type

WHERE
pc.emp_no = --@EnterEmpNo
6111
AND
CONVERT(smalldatetime, pc.check_date)
BETWEEN --@EnterStartingCheckDate
'2000-01-01 00:00:00'
AND --@EnterEndingCheckDate
'2000-06-30 00:00:00'

GROUP BY
pc.emp_no
, pcpa.pay_type
, pt.description

 

[SQLSister]

views cannot have changeable parameters. Why woudl you want to change it to a view if you already have it as a stored proc? You would gain nothing by doing so.

"NOTHING is more important in a database than integrity." ESquared

 

[tgtfranz]

I created a table function and that allowed me to pass the parameters into the dataset that I needed. Now all I have to do is figure out how to call them in my Access form. Any suggestions?

 

[tgtfranz]

I managed to get this into Access but now I am getting the error: Insufficient number of arguments were supplied for procedure or function.

When I run this in SQL it returns exactly what I want with the exact same parameters that I am entering in Access. I will also add this one to the Access Forum just in case.

Thanks

 

[danvlas]

InputParameters property of the form
Or...leave RecordSource blank and set it in code:

Me.Recordsource="Select * From dbo.FunctionName(" & Me.EmpNo & ", '" & Me.StartDate & "', '" & Me.EndDate & "')"

Make sure that the dates are passed correctly - you can use the Format function

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[tgtfranz]

Here is what I added to the record source field in the properites of my subform. Is this not the right place to add this?

Me.Recordsource="Select * From dbo.PopulatePayTypesandHoursSummaryByDateRange(" & Me.EnterEmpNo & ", '" & Me.@EnterStartingCheckDate & "', '" & Me.@EnterEndingCheckDate & "')"

I am not sure what you mean to verify my date formats. Where would I do this? These are the same dates that are being passed into the main form and return the data that I need so I would assume that the format is correct. However I still would like to know where to go to check this out.

Thanks
Daniel

 

[LNBruno]

This seems like it would be more appropriately posted in one of the Microsoft Access forums.

And isn't this the same thing you were talking about in this thread:

http://www.tek-tips.com/viewthread.cfm?qid=1410567&page=1

< M!ke >
[small]I can say nothing, which is cowardly, I can lie, which is immoral, or I can tell the truth, which will upset people. - Tiki Barber[/small]