SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calling Scalar Function within stored Procedure


Calling Scalar Function within stored Procedure

Author
Message
mandavli
mandavli
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 200
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28810 Visits: 39984
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mandavli
mandavli
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 200
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
Elliott Whitlow
Elliott Whitlow
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10288 Visits: 5314
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
Joey Morgan
Joey Morgan
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 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))
Joey Morgan
Joey Morgan
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 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))
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search