How to call a user define function(udf) from Store Procedure:-

Hello Friends,

                      In this Chapter  we will discussed  how  a function will be call  from  Store Procedure in Sql server.First we should know what types of function is availablein sqlserver.

Mainly there are two types of functions in sqlserver database 

1>System define function

2>User Define Function(UDF)

System define function:

           Basically System Define Functions are the built-in function which is provided by microsoft like aggregate function,Configuration function,cursor function,mathematical function,date and time functions  etc…

User Define Function(UDF):

      UDF is created by user mainly it categorized  into three types:

1>scalar function

      Scalar function is that types of function which return a single value through RETURN clause.

2>Inline table-valued function:

          But in Inline table-valued function  has no function body.It contains result set of single select statement and table valued function return a table

3>multistatement table-valued function:

               In  this function  function body defined in a Begin - End Block,which is contains transact-sql statements.

Next Chapter we will briefly discuse  with valuable example,But now I will create a simple scalar function  and call it from store procedure.please follow this step…….

 Step:-1

 Open  your sql server management  studio and create a data base (suppse provide name sqlcall)

->Right click on your data base and select New query .

write following code to create a scslar function.

Create function numadd

(

       @num_a int,

       @num_b int

)

RETURNS int

AS

BEGIN

       -- Declare  return variables

       DECLARE @sumtotal int

       SELECT @sumtotal = @num_a + @num_b;

       -- Return sumtotal value of the function

       RETURN @sumtotal

END

Press F5 to execute

  Step:-2

Write the code to create procedure and call our function

Create Procedure  funcall

(

@num_a int,

@num_b int

)

AS

begin

declare @find int

select dbo.numadd(@num_a, @num_b)as sumtotalsofnumbers

end

Press F5 to execute

Step:-3

After create function and procedure write the following code to find your result.

USE sqlcall

GO

DECLARE  @return_sumtotalvalue  int

EXEC  @return_sumtotalvalue = [dbo].funcall

       @num_a = 500,

    @num_b = 700

Press F5 to execute the statement,

After execute the o/p will show like this.

 

  I have explained how to call a user define function(udf) from store procedure.So after read please Comments and suggestions.

Thanks & Regard

Rabi