Calling Scalar Function within stored Procedure

• mandavli

  SSCrazy

  Points: 2122

  More actions

  January 6, 2011 at 9:36 am

  #229910

  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

  SSC Guru

  Points: 323504

  More actions

  January 6, 2011 at 9:58 am

  #1270186

  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

  SSCrazy

  Points: 2122

  More actions

  January 6, 2011 at 10:09 am

  #1270189

  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

  SSC Guru

  Points: 102296

  More actions

  January 6, 2011 at 12:21 pm

  #1270283

  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

  SSC-Addicted

  Points: 458

  More actions

  February 19, 2014 at 9:22 am

  #1690350

  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

  SSC-Addicted

  Points: 458

  More actions

  February 19, 2014 at 9:23 am

  #1690352

  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))