Help coverting a stored procedure to a view

Can someone help me convert this stored procedure to a view? It is using two UDFs.

I appreciate this very much!

@.Startdatetime, @.End datetime ASSELECTC.Client_ID, (SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))AS SumOfpmts, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title,dbo.getLevel(SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))as pmtLevel, dbo.getLevelDesc(SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))as Description FROMtblClients CINNERJOIN tblPMTs PON C.Client_ID = P.Client_IDINNERJOIN tblPMTReceipts PRON P.PMT_ID = PR.PMT_IDINNERJOINtblClientAddresses AON C.Client_ID = A.Client_IDLEFTOUTER JOINtblSoftCreditsPMTS SCON C.Client_ID = SC.SC_Client_IDWHERE(PR.PaymentDateBETWEEN @.StartAND @.End)GROUPBY C.Client_ID, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.TitleORDERBY pmtLevelRETURN

Hi,

That stored procedure cannot be converted into a View because of the the parameters in the WHERE clause and the fact that there is a Group By that hides the PaymentDate from the results.

SQL Views do not support parameters. Because the PaymentDate is not part of the output the users of your View would not be able to provide filter by PaymentDate.

The alternative is to convert it to a SQL function that returns a table:

CREATE FUNCTION dbo.MyFunction(@.Startdatetime,
@.End datetime)

RETURNS TABLE
AS
RETURN

SELECT

C.Client_ID, (SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))AS SumOfpmts, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title,
dbo

.getLevel(SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))as pmtLevel,
dbo

.getLevelDesc(SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))as Description
FROM

tblClients CINNERJOIN
tblPMTs P

ON C.Client_ID = P.Client_IDINNERJOIN
tblPMTReceipts PR

ON P.PMT_ID = PR.PMT_IDINNERJOIN
tblClientAddresses A

ON C.Client_ID = A.Client_IDLEFTOUTER JOIN
tblSoftCreditsPMTS SC

ON C.Client_ID = SC.SC_Client_ID

WHERE

(PR.PaymentDateBETWEEN @.StartAND @.End)
GROUP

BY C.Client_ID, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title

This would allow you to pass parameters to the function from a SELECT statement a get a result set without the need to run a stored procedure.

Example of how you would use this function:

SELECT * FROM dbo.MyFunction('1/1/2007','6/1/2007')

Hope this helps!

David

|||

Thank you for sharing with me. I don't think I will be able to use the Table Valued Function with the reporting tool I have. It only works with tables and views.

|||

What is your reporting tool?