How can I call function in a stored procedure in SQL Server?

Showing Answers 1 - 4 of 4 Answers

Roopesh Nanaware

  • Oct 25th, 2006
 

CREATE PROCEDURE [usp_select_Act_Actual_Customer]
  (@BranchID    [int],
  @CompanyID    [tinyint])
--WITH ENCRYPTION
AS  
 BEGIN 
  SET NOCOUNT ON
  
  DECLARE @Nulldate [smalldatetime]
  SET @Nulldate  = 0
  
  DECLARE @Serverdate  [smalldatetime]
  SET @Serverdate  = GETDATE()

  SELECT s.[BranchID], s.[CompanyID], s.[FinancialyearID],
  s.[Saledate], s.[Invoiceno], s.[Goodscategoryname],
  s.[Bankname], s.[Customercode], s.[Saletype],
  s.[Customername], s.[Duedays],   
  s.[Duedate], s.[Countryname], s.[Totalquantity],
  s.[Netvalue], s.[NetvalueInLocal],
  s.[Netreceiptamount], s.[Exchangeratetolocal]
  FROM [dbo].[fn_Actual_Tsale](0, @BranchID, @CompanyID) s 
  WHERE s.[Receivablefromagent]  <> 1

  ORDER BY s.[Customername], s.[Saledate]
  RETURN(@@ERROR)
 END 

Ans: This is Procedure And "here we Taking record from (fn_Actual_Tsale) function"

  Was this answer useful?  Yes

A function can be called in a select statement as well as in a stroed proc. As function call would return a value we need to store return value in a variable.

Ex:
Assume we have a fuction .. FN_TEST(Date IN VARCHAR2, ID OUR VARCHAR2) RETURN VARCHAR2.
within stored proc declare a variable to store ID.

ID_VAL VARCHAR2(10) :=  NULL;

ID_VAL :=  FN_TEST('1-may-2008', ID_VAL);

hope this helps

Manas

  • Jun 5th, 2015
 

Code
  1. SET @val = SELECT * dbo.fnTest(@testParam);

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions