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

Stored Procedure

What is Stored Procedure?

Difference Between Function and Stored Procedure?


 

What is Stored Procedure?

A stored procedure is the saved collection of T-SQL statements in the SQL Server database that can take and return user-supplied parameters. Stored Procedures (also called sproc) can have input parameters and output parameters. Same stored procedure can be used over the network by several clients by passing different input data for input parameters. When a stored procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance.

Here is the syntax of SPROC:

CREATE PROC [ EDURE ] [owner.] ProcedureName
[ { @parameter data_type } [= DefaultValue] [OUTPUT] ] [ ,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
AS
BEGIN
   T-SQL-statements
END

Here is an example:
CREATE PROC ProductList (
   @ProductType varchar(30) = 'Electronics')
WITH ENCRYPTION
AS
BEGIN
   SELECT
     ProductName, ProductType,
     Model, UnitPrice
   FROM Products (NOLOCK)
   WHERE ProductType = @ProductType
END

How to execute:
-- Return all the products belongs to Electronics
EXECUTE ProductList
GO

-- Return all the products belongs to Auto Mobiles
EXECUTE ProductList 'Auto Mobiles'
GO


SQL Statement Limitations in a Procedure
Any SET statement can be specified inside a stored procedure except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL, which must be the only statements in the batch.

Inside a stored procedure, object names used with certain statements must be qualified with the name of the object owner if other users are to use the stored procedure. The statements are:
•ALTER TABLE
•CREATE INDEX
•CREATE TABLE
•All DBCC statements
•DROP TABLE
•DROP INDEX
•TRUNCATE TABLE
•UPDATE STATISTICS

Permissions
CREATE PROCEDURE permissions default to members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles. Members of the sysadmin fixed server role and the db_owner fixed database role can transfer CREATE PROCEDURE permissions to other users. Permission to execute a stored procedure is given to the procedure owner, who can then set execution permission for other database users.

Granting Execute permission on All Stored Procedure

How to grant Execute permission on All Stored Procedure for a new User in SQL Server?


Most of the application have users who are not creator (owner) of the Stored Procedures. Hence the users don't have EXECUTE permission on any of the Stored Procedures by default, considering that they are not part of any existing Role that has execute permission.
 
I have created a small script which returns a string that grants permission to the new User to all the Stored Procedures. Here is the script:
 
SELECT 'GRANT EXECUTE ON ' + name + ' [UserName]'
FROM sysobjects WHERE xtype IN ('P')
 
You need to copy the script generated by above query and execute. Alternatively, you can use below script - where you need not to copy the script generated by above query. Just execute the below script in required database.

DECLARE @UserName [varchar](128), @SQL [varchar](1000)
SET @UserName = '[UserName]'


DECLARE Cur CURSOR FOR
   SELECT 'GRANT EXEC ON ' +
   '[' + u.[Name] + ']' + '.' +
   '[' + o.[Name] + ']' +
   ' TO ' + @UserName  AS SQLCommand
FROM dbo.sysobjects o
JOIN dbo.sysusers u
  ON o.[uid] = u.[uid]
WHERE o.[Type] = 'P'

OPEN Cur
FETCH NEXT FROM Cur INTO @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT @SQL
   EXEC (@SQL)
   FETCH NEXT FROM Cur INTO @SQL
END
CLOSE Cur
DEALLOCATE Cur

Cheers !!!