Difference between Function and Stored Procedure?

• User Defined Function (UDF) can be used in the SQL statements anywhere in the SELECT, WHERE, and HAVING section where as Stored procedures cannot be used.
• Functions are designed to send their output to a query or T-SQL statement while Stored Procedures use EXECUTE or EXEC to run.
• We can not use EXECUTE and PRINT commands inside a function but in SPROC
• UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
• UDFs can't change the server environment or your operating system environment, while a SPROC can.
• Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
• Stored Procedures are stored in compiled format in the database where as Functions are compiled and excuted runtime.
• SPROC can be used with XML FOR Clause but Functions can not be.
• SPROC can have transaction but not Functions.
• Functions can be used in a SPROC but SPROC cann't be used in a Function. Only extended stored procedures can be called from a function.
• Of course there will be Syntax differences and here is a sample of that

CREATE PROCEDURE dbo.ProcedureName
(
   @parameter1 datatype = DefaultValue,
   @parameter2 datatype OUTPUT
)
AS 
BEGIN
   T-SQL statements
   RETURN
END
GO

CREATE FUNCTION dbo.FunctionName
(
   @parameter1 datatype = DefaultValue,
   @parameter2 datatype
)
RETURNS datatype
AS
BEGIN
   SQL Statement
   RETURN Value
END
GO