September 6, 2013 at 4:56 am
Hi all,
I was creating a stored procedure but in this sp for some columns I need to call a function and then assign that value to a column.
here is procedure that I am writing:
select
OFC.CompanyID,
PRAD.ProjectID,
PRAD.ProposalID,
P.ProposalNo,
P.ProposalName,
PRAD.DateAdded,
(P.Pri_Actual_TSP + PINFO.Pri_Actual_TSP) AS TSP,
P.Pri_QSP + PINFO.Pri_QSP AS QSP,
DiscountPercent -- for this column I need to call a function to get the value fn_GetPercentile ( Proposal.Pri_Actual_TSP + ProposalInformation.Pri_Actual_TSP, Proposal.Pri_QSP + ProposalInformation.Pri_QSP
FROM ProposalRequestApprovalDetails PRAD
INNER JOIN ProcessProposalApprovalDetailsCLC PDC
ON PDC.ProjectID = PRAD.ProjectID
AND PDC.ProposalID = PRAD.ProposalID
INNER JOIN Proposal P ON P.ProjectId = PRAD.ProjectID
AND P.ProposalId = PRAD.ProposalID
AND P.OfficeId = PRAD.OfficeID
INNER JOIN ProposalInformation PInfo ON PINFO.OfficeId = P.OfficeId
AND PINFO.ProjectID = P.ProjectId
AND PINFO.ProposalId = P.ProposalId
INNER JOIN vw_Office OFC ON OFC.OfficeID = PRAD.OfficeID
How can i achieve this?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 6, 2013 at 5:24 am
I think I've misunderstood your requirements but this is how you can call a function
create table #tmp
(
f1 int null,
f2 int null
)
GO
insert into #tmp values (1,2), (2,9), (3, 15)
GO
create function dbo.myAdd(@val1 int, @val2 int)
returns int
AS
begin
DECLARE @Tmp int
SET @Tmp = ISNULL(@val1,0) + ISNULL(@val2,0)
return @Tmp
end
GO
select f1, f2, dbo.myAdd(f1,f2) as theirSum
from #tmp
drop table #tmp
drop function dbo.myAdd
September 6, 2013 at 6:14 am
Are you sure you need a function? I think you need a little nudge in the right direction with APPLY:
SELECT
OFC.CompanyID,
PRAD.ProjectID,
PRAD.ProposalID,
P.ProposalNo,
P.ProposalName,
PRAD.DateAdded,
x.TSP,
x.QSP,
DiscountPercent = x.TSP + x.QSP
FROM ProposalRequestApprovalDetails PRAD
INNER JOIN ProcessProposalApprovalDetailsCLC PDC
ON PDC.ProjectID = PRAD.ProjectID
AND PDC.ProposalID = PRAD.ProposalID
INNER JOIN Proposal P ON P.ProjectId = PRAD.ProjectID
AND P.ProposalId = PRAD.ProposalID
AND P.OfficeId = PRAD.OfficeID
INNER JOIN ProposalInformation PInfo ON PINFO.OfficeId = P.OfficeId
AND PINFO.ProjectID = P.ProjectId
AND PINFO.ProposalId = P.ProposalId
INNER JOIN vw_Office OFC ON OFC.OfficeID = PRAD.OfficeID
CROSS APPLY (
SELECT
TSP = (P.Pri_Actual_TSP + PINFO.Pri_Actual_TSP),
QSP = (P.Pri_QSP + PINFO.Pri_QSP)
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy