Calling Scalar Function within stored Procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Calling Scalar Function within stored...

Calling Scalar Function within stored Procedure

Rate Topic

Display Mode

Topic Options

Author

Message

mandavli

Posted Thursday, January 06, 2011 9:36 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 8:26 AM
Points: 44, Visits: 128

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

Lowell

Posted Thursday, January 06, 2011 9:58 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 7:02 AM
Points: 11,791, Visits: 28,069

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

mandavli

Posted Thursday, January 06, 2011 10:09 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 8:26 AM
Points: 44, Visits: 128

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

Elliott Whitlow

Posted Thursday, January 06, 2011 12:21 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:09 AM
Points: 5,863, Visits: 4,886

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

« Prev Topic | Next Topic »

Permissions