Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calling Scalar Function within stored Procedure Expand / Collapse
Author
Message
Posted Thursday, January 06, 2011 9:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, December 08, 2013 11:46 AM
Points: 50, Visits: 141
I have written scalar function named getCalcvalue that accepts 2 parameters and returns one parameters.
Now I want to call this from stored procedure.
so I have statement

declare @selVal as integer
set @selVal = select getCalcvalue(param1,param2)

but I get error 'getCalcvalue' is not a recognized built-in function name.

So I tried
set @selVal = select dbo.getCalcvalue(param1,param2)

Then also I get error How do I call this fuction correctly
Post #1043856
Posted Thursday, January 06, 2011 9:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:16 PM
Points: 12,744, Visits: 31,079
we'd really need the exact error you are getting...and also the exact code you are doing to really help. All i can offere is generalities.

where is param1 and param2? are they fields, or locally declared variables inside the proc?
CREATE PROC myProc AS
BEGIN
declare @Param1 int
Declare @Param2 int
'assign values to variables inside the proc?
select @Param1 = object_id,@Param2 = colid from sys.columns where object_name(object_id)= 'myTable'

declare @selVal as integer
set @selVal = select dbogetCalcvalue(@Param1,@Param2)
--do more stuff
END



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1043864
Posted Thursday, January 06, 2011 10:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, December 08, 2013 11:46 AM
Points: 50, Visits: 141
Here is my function and stored procedure


Create Function getCalcvalue
(@Param1 as integer,
@Param2 as integer ) returns integer
Begin
declare @setVal as integer
-- select techVal from Pt101Tbl where col1 = @param1 and col3 = @param2
set @setval = ---calculations based on techVal
return @setVal
End

ALTER PROCEDURE [dbo].[sp1]
@value1 as integer,
@value2 as integer,
@value3 as integer
AS
begin
declare @setval as integer
set @setVal = getCalcvalue(@value1,@value2)
-- based on value i receive from function there are fuether select statements
End
Post #1043868
Posted Thursday, January 06, 2011 12:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
mandavli (1/6/2011)
begin
declare @setval as integer
set @setVal = getCalcvalue(@value1,@value2)
-- based on value i receive from function there are fuether select statements


How about:
SELECT @setVal = dbo.getCalcvalue(@value1,@value2)

CEWII
Post #1043978
Posted Wednesday, February 19, 2014 9:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 3:32 PM
Points: 26, Visits: 34
First off, it is good practice to use meaningful names for your parameters so when you revisit your code next year or someone else does they know what is expected.
That said, put parentheses around your function result:
Set @desiredValue=(SELECT @ScalarFunctionResult(@Param1,@Param2))

Post #1543096
Posted Wednesday, February 19, 2014 9:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 3:32 PM
Points: 26, Visits: 34
mandavli (1/6/2011)
I have written scalar function named getCalcvalue that accepts 2 parameters and returns one parameters.
Now I want to call this from stored procedure.
so I have statement

declare @selVal as integer
set @selVal = select getCalcvalue(param1,param2)

but I get error 'getCalcvalue' is not a recognized built-in function name.

So I tried
set @selVal = select dbo.getCalcvalue(param1,param2)

Then also I get error How do I call this fuction correctly

set @selVal = (select dbo.getCalcvalue(param1,param2))
Post #1543098
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse