none
Difference between Function and Stored Procedure

    Question

  • Hi guys, i don't understand the exact difference between a function and a stored procedure. I did lot of google but still. Can somebody explain in simple words. Thanks.
    svk
    Tuesday, August 16, 2011 2:00 PM

Answers

  • Quick (and very crude) example :

    CREATE FUNCTION dbo.fnMultiplier (@Input INT)
    RETURNS INT
    WITH EXECUTE AS CALLER
    AS
    BEGIN
    	RETURN (@Input * 10)
    END
    GO
    
    CREATE TABLE #Test1 (Id INT)
    INSERT INTO #Test1 SELECT 1 UNION SELECT 2 UNION SELECT 3
    
    SELECT Id FROM #Test1
    SELECT dbo.fnMultiplier(Id) AS MultiplierId FROM #Test1
    
    DROP FUNCTION dbo.fnMultiplier
    DROP TABLE #Test1


    Clive
    http://www.sqlsvrdba.com
    • Marked as answer by czarvk Tuesday, August 16, 2011 3:55 PM
    Tuesday, August 16, 2011 2:13 PM
  • On the surface, it can look like they do a lot of the same thing, but there are some big differences.

    Functions can return tables or just a single field in a single row (scalar). But then, so can procedures.

     

    One of the great things about functions is that you can use them in a query as if they were a field:

    SELECT field1, field2, dbo.ufn_MyFunc(Field2)
    FROM table
    

    Procedures must be executed using the EXEC keyword, and use either @parameters or just listing the values for the parameters in the order in which they are specified:

    exec usp_MyProcedure @param = 'Test'
    

    There are other differences, too. You can use temporary tables in stored procedures, but not in functions. You can specify the type of returned data type with a function, but not with stored procedures. A great way to learn more is to try making one of each with the same goals in mind.

     

    • Marked as answer by czarvk Tuesday, August 16, 2011 3:55 PM
    Tuesday, August 16, 2011 2:31 PM
  • There are other differences, too. You can use temporary tables in stored procedures, but not in functions. You can specify the type of returned data type with a function, but not with stored procedures. A great way to learn more is to try making one of each with the same goals in mind.


    Hallo rsims,

    your statement is not true - you can use "temporay" tables as variables!

     

    USE tempdb
    GO
    
    CREATE FUNCTION dbo.fn_sys_MyValue(@SId int) RETURNS nvarchar(255)
    AS
    BEGIN
    	DECLARE	@ReturnValue	nvarchar(255)
    	
    	DECLARE @Table TABLE
    	(
    		Id		int,
    		MyText	nvarchar(255)
    	)
    	
    	INSERT INTO @table
    	(Id)
    	VALUES (@SId)
    	
    	UPDATE @table
    	SET	MyText = 'Test it!'
    	
    	SELECT @ReturnValue = MyText
    	FROM	@table
    	
    	RETURN @ReturnValue
    END
    GO
    
    -- Test
    SELECT	dbo.fn_sys_MyValue(1)
    

    I know it's a stupid example but it demonstrate the usage of a TABLE-Variable...

     


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITS Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de

    • Marked as answer by czarvk Tuesday, August 16, 2011 3:55 PM
    Tuesday, August 16, 2011 2:47 PM
  • > All your answers are very much helpful guys. If a stores procedure doesn't return a value, view doesn't either. So now whats the difference between stored procedure and a view. Thanks guys i really appreciate your concern and help.

    A view is a stored query with a known result set. That is, you can query metadata to see what columns the view will return.

    A stored procedure can return anything. It can return zero to many result sets. And the result sets can be different everytime, although in many cases they are not.

    Stored procedures is the general vehicle to put code in SQL Server. Functions are more specialised. In the system I work with there are over 6500 stored procdures, but less than 100 functions. You are also quite constraint in what you can do in a function.

    It is also worth mentioning that there are different types of functions. An inline table function is parameterised view with a different name.

    Functions can be useful, but incorrectly use they can cause performance issues. Scalar functions with data access is something to avoid.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by czarvk Tuesday, August 16, 2011 3:55 PM
    Tuesday, August 16, 2011 3:19 PM

All replies

  • A function is like a stored procedure in many ways.  You can pass in input parameters and you can return an output.

    However, with a function, you can pass it in as part of a query to execute the function on the fly.

    For example, lets say you write a function to multiply the input parameter by 10.  We'll call the function fnMultiplier.

    If you run, SELECT Id FROM TestTable and it returns the values 1, 2, 3.

    Now lets run SELECT fnMultiplier(Id) FROM TestTable, it will return 10, 20, 30.


    Clive
    http://www.sqlsvrdba.com
    Tuesday, August 16, 2011 2:07 PM
  • Quick (and very crude) example :

    CREATE FUNCTION dbo.fnMultiplier (@Input INT)
    RETURNS INT
    WITH EXECUTE AS CALLER
    AS
    BEGIN
    	RETURN (@Input * 10)
    END
    GO
    
    CREATE TABLE #Test1 (Id INT)
    INSERT INTO #Test1 SELECT 1 UNION SELECT 2 UNION SELECT 3
    
    SELECT Id FROM #Test1
    SELECT dbo.fnMultiplier(Id) AS MultiplierId FROM #Test1
    
    DROP FUNCTION dbo.fnMultiplier
    DROP TABLE #Test1


    Clive
    http://www.sqlsvrdba.com
    • Marked as answer by czarvk Tuesday, August 16, 2011 3:55 PM
    Tuesday, August 16, 2011 2:13 PM
  • But if you create the function fnMultiplier as a procedure you can't access in a query. This is the main difference between Proc and function.

    Tuesday, August 16, 2011 2:21 PM
  • On the surface, it can look like they do a lot of the same thing, but there are some big differences.

    Functions can return tables or just a single field in a single row (scalar). But then, so can procedures.

     

    One of the great things about functions is that you can use them in a query as if they were a field:

    SELECT field1, field2, dbo.ufn_MyFunc(Field2)
    FROM table
    

    Procedures must be executed using the EXEC keyword, and use either @parameters or just listing the values for the parameters in the order in which they are specified:

    exec usp_MyProcedure @param = 'Test'
    

    There are other differences, too. You can use temporary tables in stored procedures, but not in functions. You can specify the type of returned data type with a function, but not with stored procedures. A great way to learn more is to try making one of each with the same goals in mind.

     

    • Marked as answer by czarvk Tuesday, August 16, 2011 3:55 PM
    Tuesday, August 16, 2011 2:31 PM
  • All your answers are very much helpful guys. If a stores procedure doesn't return a value, view doesn't either. So now whats the difference between stored procedure and a view. Thanks guys i really appreciate your concern and help.
    svk
    Tuesday, August 16, 2011 2:36 PM
  • All your answers are very much helpful guys. If a stores procedure doesn't return a value, view doesn't either. So now whats the difference between stored procedure and a view. Thanks guys i really appreciate your concern and help.
    svk


    A view can be joined with other objects of type U, V, IF. See a proc as a BATCH which handles multiple T-SQL command sequential.


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITS Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de

    Tuesday, August 16, 2011 2:41 PM
  • There are other differences, too. You can use temporary tables in stored procedures, but not in functions. You can specify the type of returned data type with a function, but not with stored procedures. A great way to learn more is to try making one of each with the same goals in mind.


    Hallo rsims,

    your statement is not true - you can use "temporay" tables as variables!

     

    USE tempdb
    GO
    
    CREATE FUNCTION dbo.fn_sys_MyValue(@SId int) RETURNS nvarchar(255)
    AS
    BEGIN
    	DECLARE	@ReturnValue	nvarchar(255)
    	
    	DECLARE @Table TABLE
    	(
    		Id		int,
    		MyText	nvarchar(255)
    	)
    	
    	INSERT INTO @table
    	(Id)
    	VALUES (@SId)
    	
    	UPDATE @table
    	SET	MyText = 'Test it!'
    	
    	SELECT @ReturnValue = MyText
    	FROM	@table
    	
    	RETURN @ReturnValue
    END
    GO
    
    -- Test
    SELECT	dbo.fn_sys_MyValue(1)
    

    I know it's a stupid example but it demonstrate the usage of a TABLE-Variable...

     


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITS Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de

    • Marked as answer by czarvk Tuesday, August 16, 2011 3:55 PM
    Tuesday, August 16, 2011 2:47 PM
  • > All your answers are very much helpful guys. If a stores procedure doesn't return a value, view doesn't either. So now whats the difference between stored procedure and a view. Thanks guys i really appreciate your concern and help.

    A view is a stored query with a known result set. That is, you can query metadata to see what columns the view will return.

    A stored procedure can return anything. It can return zero to many result sets. And the result sets can be different everytime, although in many cases they are not.

    Stored procedures is the general vehicle to put code in SQL Server. Functions are more specialised. In the system I work with there are over 6500 stored procdures, but less than 100 functions. You are also quite constraint in what you can do in a function.

    It is also worth mentioning that there are different types of functions. An inline table function is parameterised view with a different name.

    Functions can be useful, but incorrectly use they can cause performance issues. Scalar functions with data access is something to avoid.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by czarvk Tuesday, August 16, 2011 3:55 PM
    Tuesday, August 16, 2011 3:19 PM
  • Just to add one point:

    Functions cannot themselves update or alter (insert/delete) table data (except for local table variables) and by extension cannot call stored procedures.  If you want to do that, you have to use a stored procedure.  (However, you use a table function just like a table or view for joins etc. so very convenient.  You can't do that with sprocs.)

    (It's worth repeating Sommarskog's point: If using functions, for performance, always try to use inline table-valued functions, i.e. not multi-line table functions and not scalar functions.  It can make a big difference.  All scalar functions can, and probably should, be replaced by inline table functions, at least based on the last time I tested.  I don't know where to find the best performance testing results on this, but there are some huge differences.)

     

     

     

    Tuesday, August 16, 2011 5:54 PM