How to call function results into SP select statement

Question

Post reply

How to call function results into SP select statement

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
September 26, 2016 at 2:00 pm

#400774

How to call function in a select statement of the stored procedure.
I have a function where it takes Des1 and Des2 as parameters.From the query of the function i will get 2 results based on these parameters.
one is code another one is codedesc ( these are the columns selecting from function) and I want to use these columns in above stored procedure.
now i have to call these two columns in the above stored procedure in select statement.How to call this?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Err, what do you mean by 'select statement of the stored procedure'?

I assume this is a table-valued UDF. If so, then it's used in the FROM clause of a select

SELECT FunctionColumn1, FunctionColumn2, ... FROM dbo.UserDefinedFunctionName(Parameter1, Parameter2, ...)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 2

Yes,This is UDF. I want to use this function in my stored procedure.

FUNCTION dbo.Blabla

(

@DE1 Varchar(100),

@DE2 Varchar(100)

)

RETURNS TABLE

AS

RETURN

(

SELECT

substring(CAST(COL1 AS VARCHAR(20)),1,CAST(CHARINDEX('-',COL1) AS VARCHAR(20)) -1) AS ,

substring(COL1,CHARINDEX('-',COL1)+1,(Len(COL1))) AS [lCode]

FROM [Emp] A

Where DE1=@DE1 and DE2=@DE2

AND DT = (Select MAX(DT) From dbo.Emp B Where A.DE1=B.DE1 and A.DE2=B.DE2)

)

Now i want to use this function in my stored procedure to get code and lcode values?

How to approach this based on parameters?

pietlinden SSC Guru Points: 63516 More actions · Answer 3

Since your UDF returns a table, you could probably use CROSS APPLY to pass values from the other table (the source for the two parameters) and then return the whole thing in one result set. Kind of hard to tell with the sketchy description of the problem.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

mcfarlandparkway (9/26/2016)
Now i want to use this function in my stored procedure to get code and lcode values?

With the really vague question and lack of details, hard to say anything useful. Can you explain further?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MadAdmin SSChampion Points: 11261 More actions · Answer 5

create procedure ProcBlah(@param1 varchar(100),@param2 varchar(100))

as

BEGIN

Select code,icode

from dbo.Blabla(@param1,@param2)

END

GO

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]