get value from a function in a Stored Procedure

Question

Post reply

get value from a function in a Stored Procedure

kapil_kk

SSC-Insane

Points: 21316
More actions
September 6, 2013 at 4:56 am

#395432

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/

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

terry999 SSCertifiable Points: 5036 More actions · Answer 1

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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