The Differences between functions and stored procedures



Functions (select data only)
SPs (can modify data)
Purpose
For returning a computation value used in other T-SQL statements
For performing business logic with a set of complex T-SQL statements, or interacting with outside world.
Which T-SQL elements can be used inside?
SELECT only (cannot modify the data or objects)
DML, DDL, Transaction, Error Handling
How to be used?
SELECT, WHERE, HAVING
EXEC (for deterministic functions)
EXEC
Deferred Name Resolution?
No
Yes
Need input parameters?
Optional (0 ~ 1023)
Optional (0 ~ 21000)
Which SQL Server data types can be used as input parameters?
All including table variable but not text, ntext, image, timestamps
Any SQL Server data types including table variable (companying with READONLY).
Have to return a data?
Yes (a single value or a table)
Optional (0 ~ Many) – using OUTPUT or OUT
Which SQL Server data types can be used as output parameters?
All including table variable but not text, ntext, image, timestamps
Any SQL Server data types, but not a table variable. Cursor is used as output only accompanied by the VARYING keyword.
Return a status code?
No
Yes
Can a temp table be used inside?
No (but table variable is OK)
Yes (table variable OK too)
Can call another function
Yes
Yes
Can call another SP?
No (but can call an extended stored procedure.)
Yes
Can be used for a dynamic SQL?
No
Yes
Can a non-deterministic function be used?
No
Yes
Typical use cases

  • SELECT – inline function
  • WHERE
  • HAVING
SQL Codes in the applications vs. Stored Procedure in SQL Server?

The benefits of using stored procedures in SQL Server rather than application code stored locally on client computers include:

  • They allow modular programming.
  • They allow faster execution.
  •  They can reduce network traffic.
  • They can be used as a security mechanism.
  •  Protect against SQL injection attacks.
When to use SPs? – if the codes need to be reused

  • If the operation requires a large amount of T-SQL code or is performed repetitively,
  • If an operation requires hundreds of lines of T-SQL code through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.
  • If you want to grant users permission to execute a stored procedure but not permission to execute the procedure's statements directly.
  • In dynamic SQL,if you want to pass data values to a backend stored procedure.
When to place SQL directly in your application? – If the codes are dynamic

  • When the SQL is generated on the fly. That is, you cannot create and cache an execution plan since it's different each time it's called.  Or the code is executed will depend on your environment (i.e., the server). Its dynamic nature negates many of the advantages of a stored procedure.
  •  In dynamic SQLif you want to pass data values to the application code to construct the T-SQL string.
Caveat

  • Use other options instead of UDF if it makes the query non-sargable or cursor-type.
  • Create the function with SchemaBinding

  • Include SET NOCOUNT ON statement
  •  Use schema name with object name
  • Generally do not use the prefix “sp_” in the stored procedure name
  • Use IF EXISTS (SELECT 1) instead of (SELECT *)
  • Use the sp_executesql stored procedure instead of the EXECUTE (@AString)
  • Try to avoid using SQL Server cursors whenever possible
  •  Keep the Transaction as short as possible
  • Use TRY-Catch for error handling

How to find the owner of an object and how to change the owner in SQL Server?

In SQL Server 2000, users own object. In SQL Server 2005 and onward, users do not own objects, instead, they own schema. Thus, by default, the owner of an object should be the owner of its schema. But you can change it if necessary.

1. How to find the owner of an object

SELECT o.object_id,
   o.type,
   o.name,
   CASE
     WHEN principal_id IS NOT NULL
                  THEN (SELECT name
                  FROM   sys.database_principals dp
                  WHERE  dp.principal_id = o.principal_id)
     ELSE (SELECT dp.name
              FROM   sys.database_principals dp,
                  sys.schemas s
              WHERE  s.schema_id = o.schema_id
                  AND s.principal_id = dp.principal_id)
   END AS Owner
FROM   sys.objects o
--WHERE type='U'
ORDER  BY o.name
GO

(http://www.sqlservercentral.com/Forums/Topic324773-149-1.aspx)

Or more concisely as below:

SELECT 
    so.[name] AS [Object Name]
  , sch.[name] AS [Schema Name]
  , USER_NAME(COALESCE(so.[principal_id], sch.[principal_id])) AS [Owner]
  , type_desc AS [ObjectType]
FROM sys.objects so
  JOIN sys.schemas sch
    ON so.[schema_id] = sch.[schema_id]
     order by so.name


http://www.mssqltips.com/sqlservertip/1778/ownership-chaining-in-sql-server-security-feature-or-security-risk/

2.   How to change the object owner?

SQL Server 2000 (use sp_changeobjectowner)
SQL Server 2005 and Up (use ALTER SCHEMA and ALTER AUTHORIZATION)
sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'
-- Changes the owner of the authors table to Corporate\GeorgeW.
EXEC sp_changeobjectowner 'authors', 'Corporate\GeorgeW'
ALTER SCHEMA (Transact-SQL)
Transfers a securable between schemas.
/*The following example modifies the schema HumanResources by transferring the table Address from schema Person into the schema.
*/
USE AdventureWorks2012;
GO
ALTER SCHEMA HumanResources TRANSFER Person.Address;
GO
ALTER AUTHORIZATION (Transact-SQL)
Changes the ownership of a securable.
/*A. Transfer ownership of a table
The following example transfers ownership of table Sprockets to user MichikoOsada. The table is located inside schema Parts.
*/
ALTER AUTHORIZATION ON OBJECT::Parts.Sprockets TO MichikoOsada;
GO
--The query could also look like the following:
ALTER AUTHORIZATION ON Parts.Sprockets TO MichikoOsada;
GO
/*B. Transfer ownership of a view to the schema owner
The following example transfers ownership the view ProductionView06 to the owner of the schema that contains it. The view is located inside schema Production.
*/
ALTER AUTHORIZATION ON OBJECT::Production.ProductionView06 TO SCHEMA OWNER;
GO
/*C. Transfer ownership of a schema to a user
The following example transfers ownership of the schema SeattleProduction11 to user SandraAlayo.
*/
ALTER AUTHORIZATION ON SCHEMA::SeattleProduction11 TO SandraAlayo;
GO
/*D. Transfer ownership of an endpoint to a SQL Server login
The following example transfers ownership of endpoint CantabSalesServer1 to JaePak. Because the endpoint is a server-level securable, the endpoint can only be transferred to a server-level principal.
*/
ALTER AUTHORIZATION ON ENDPOINT::CantabSalesServer1 TO JaePak;
GO