How to call  SQL function from store procedure and when we use function instead of  store procedure



Hi friends today we will discuss When to use stored procedure and when to use function



In simple team
If you want to calculate some values and it's will return single value so it's not required to write all calculation
 code inside a store procedure just write a function and call them from store procedure when it's required

Suppose you are working an inventory or ERP project and in your project have over 100 report so for every 
report you need to display amount in word

Like 5010
Five thousand ten only

text  formats in your report data so just call the function what you create for generate text from your store procedure


 So for that we can write store procedure like that

 CREATEFUNCTION [dbo].[ufn_NumberToWords]
(
    @Number AS BIGINT
) 
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Below20 TABLE (ID INT IDENTITY(0,1), Word VARCHAR(32))
    DECLARE @Below100 TABLE (ID INT IDENTITY(2,1), Word VARCHAR(32))
    DECLARE @BelowHundred AS VARCHAR(126) 

    INSERT @Below20 (Word) VALUES ('ZERO')
    INSERT @Below20 (Word) VALUES ('ONE')
    INSERT @Below20 (Word) VALUES ( 'TWO' )
    INSERT @Below20 (Word) VALUES ( 'THREE')
    INSERT @Below20 (Word) VALUES ( 'FOUR' )
    INSERT @Below20 (Word) VALUES ( 'FIVE' )
    INSERT @Below20 (Word) VALUES ( 'SIX' )
    INSERT @Below20 (Word) VALUES ( 'SEVEN' )
    INSERT @Below20 (Word) VALUES ( 'EIGHT')
    INSERT @Below20 (Word) VALUES ( 'NINE')
    INSERT @Below20 (Word) VALUES ( 'TEN')
    INSERT @Below20 (Word) VALUES ( 'ELEVEN' )
    INSERT @Below20 (Word) VALUES ( 'TWELVE' )
    INSERT @Below20 (Word) VALUES ( 'THIRTEEN' )
    INSERT @Below20 (Word) VALUES ( 'FOURTEEN')
    INSERT @Below20 (Word) VALUES ( 'FIFTEEN' )
    INSERT @Below20 (Word) VALUES ( 'SIXTEEN' )
    INSERT @Below20 (Word) VALUES ( 'SEVENTEEN')
    INSERT @Below20 (Word) VALUES ( 'EIGHTEEN' )
    INSERT @Below20 (Word) VALUES ( 'NINETEEN' ) 

    INSERT @Below100 VALUES ('TWENTY')
    INSERT @Below100 VALUES ('THIRTY')
    INSERT @Below100 VALUES ('FORTY')
    INSERT @Below100 VALUES ('FIFTY')
    INSERT @Below100 VALUES ('SIXTY')
    INSERT @Below100 VALUES ('SEVENTY')
    INSERT @Below100 VALUES ('EIGHTY')
    INSERT @Below100 VALUES ('NINETY')

    IF @Number > 99
    BEGIN
        SELECT @belowHundred = dbo.ufn_NumberToWords( @Number % 100)
    END

    DECLARE @NumberInWords VARCHAR(MAX)
    
    SET @NumberInWords  = 
    (
      SELECT
        CASE 
            WHEN @Number = 0 THEN  ''
            WHEN @Number BETWEEN 1 AND 19 
                THEN (SELECT Word FROM @Below20 WHERE ID=@Number) 
            WHEN @Number BETWEEN 20 AND 99
                THEN (SELECT Word FROM @Below100 WHERE ID=@Number/10)+ '-' + dbo.ufn_NumberToWords( @Number % 10) 
            WHEN @Number BETWEEN 100 AND 999 
                THEN (dbo.ufn_NumberToWords( @Number / 100)) + ' HUNDRED '+ 
                        CASE
                            WHEN @belowHundred <> '' 
                                THEN 'AND ' + @belowHundred else @belowHundred
                        END
WHEN @Number BETWEEN 1000 AND 99999 
                THEN (dbo.ufn_NumberToWords( @Number / 1000))+ ' THOUSAND '+ dbo.ufn_NumberToWords( @Number % 1000)  
WHEN @Number BETWEEN 100000 AND 9999999 
                THEN (dbo.ufn_NumberToWords( @Number / 100000))+ ' LAKH '+ dbo.ufn_NumberToWords( @Number % 100000)  
            WHEN @Number BETWEEN 10000000 AND 999999999 
                THEN (dbo.ufn_NumberToWords( @Number / 10000000)) + ' CRORE '+ dbo.ufn_NumberToWords( @Number % 10000000) 
            ELSE ' INVALID INPUT'
        END

    )
    SELECT @NumberInWords = RTRIM(@NumberInWords)
    SELECT @NumberInWords = RTRIM(LEFT(@NumberInWords,LEN(@NumberInWords)-1)) WHERE RIGHT(@NumberInWords,1) = '-'

    RETURN (@NumberInWords)


END


so if we need to display value we call then from  store procedure like that 

Calling Sql function from store procedure

CREATE PROCEDURE [dbo].[Justfortext]
(
  @CompanyId  varchar(50),
  @CttingId varchar(50)
  
)
AS
BEGIN 
select PurchaseRate,(POQuantity*PurchaseRate)[Amount],
(select [dbo].[ufn_NumberToWords](POQuantity*PurchaseRate))
[AmountInWord] from dbo.PURCHASE_ENTRY_DETAIL
END

Output is 



You can Also watch the videos for more info




Stored Procedure Usage:
In stored procedure you can make a call to database functions.

If you want to perform any DML operation (insert, update, delete).
If you want to return multiple results along with multiple complex select functions.
Functions:
in function you cannot perform any DML Operation you can perform only read only operation.
You cannot make a call to procedure from functions.

Scalar Func: Return single primitive data type.

Table Valued Func: when you want to return single result into table by joining multiple tables. In function you can pass multiple input parameter to filter data.

Multi-Valued Table functions: A multi-statement table-valued function or MSTVF is a table-valued function that returns the result of multiple statements. ... To define a multi-statement table-valued function, you use a table variable as the return value. Inside the function, you execute one or more queries and insert data into this table variable.