SQL Procs and User Defined Functions: A Mind Map

In this post a mind map to help to memorize and visualize all the concepts around Stored Procedures and User Defined Functions, check the first post here.

SP and Usr Def
1 A batch is a sequence of Transact- SQL statements and procedural extension Batches(Relationship)
1.1 User-Defined Functionsfunctions always have one return value.
1.1.1 CREATE FUNCTIONCREATE FUNCTION [schema_name.]function_name [({@param } type [= default]) {,…} RETURNS {scalar_type | [@variable] TABLE} [WITH {ENCRYPTION | SCHEMABINDING} [AS] {block | RETURN (select_statement)} N> only the members of the sysadmin fixed server role and the db_owner and db_ddladmin fixed database roles can use it N> Each function used in a Transact-SQL statement must be specified using schema_name.function_name.
schema_name
RETURNS data type returned can be any of the standard data types supported by the database system, including the TABLE data type.
WITH ENCRYPTION
WITH SCHEMABINDINGAny attempt to modify the structure of the database object that the function references fails
blockBEGIN/END block that contains the implementation of the function only the following statements are allowed: Assignment statements such as SET DECLARE statements defining local data variables Control-of-flow statements SELECT statements containing INSERT, UPDATE, and DELETE statements
1.1.2 scalar-valued
1.1.3 table-valued
TABLE Data Type
Table-Valued ParametersBenefits: – It simplifies the programming model in relation to routines. – The resulting table can have different numbers of rows.
ex
CREATE TYPE departmentType AS TABLE (dept_no CHAR(4),dept_name CHAR(25),location CHAR(30)); 
GO 
CREATE TABLE #dallasTable (dept_no CHAR(4),dept_name CHAR(25),location CHAR(30)); 
GO 
CREATE PROCEDURE 
insertProc @Dallas departmentType READONLY 
AS 
SET NOCOUNT ON 
INSERT INTO #dallasTable (dept_no, dept_name, location) 
SELECT * FROM @Dallas 
GO 
DECLARE @Dallas AS departmentType; 
INSERT INTO @Dallas( dept_no, dept_name, location) 
SELECT * FROM department WHERE location = 'Dallas' 
EXEC insertProc @Dallas;
APPLY operator
1.2 Stored ProceduresIt is stored as a database object N> A stored procedure is precompiled before it is stored as an object in the database Stored procedures can also be used for the following purposes: – To control access authorization – To create an audit trail of activities in database tables
1.2.1 system procedures
1.2.2 parameters
1.2.3 security control
1.2.4 CREATE PROCEDURE statementCREATE PROC[EDURE] [schema_name.]proc_name [({@param1} type1 [ VARYING] [= default1] [OUTPUT])] {, …} [WITH {RECOMPILE | ENCRYPTION | EXECUTE AS ‘user_name’}] [FOR REPLICATION] AS batch | EXTERNAL NAME method_name schema_name is the name of the schema proc_name is the name OUTPUT option indicates that the parameter is a return parameter N> You can create local or global temporary procedures by preceding the procedure name with a single pound sign (#proc_name) for local temporary procedures and a double pound sign (##proc_name, for example) for global temporary procedures
WITH RECOMPILEto be used when database objects used by the stored procedure are modified frequently or when the parameters used by the stored procedure are volatile.
OUTPUT
EXECUTE AS
ex
USE sample;
GO
CREATE PROCEDURE increase_budget (@percent INT=5)
AS UPDATE project SET budget = budget + budget*@percent/100;
1.2.5 EXECUTE statement[[EXEC[UTE]] [@return_status =] {proc_name | @proc_name_var} {[[@parameter1 =] value | [@parameter1=] @variable [OUTPUT]] | DEFAULT}.. [WITH RECOMPILE]
1.2.6 ALTER PROCEDURE statement
1.2.7 DROP PROCEDURE statementOnly the owner of the stored procedure and the members of the db_owner and sysadmin fixed roles can remove the procedure.
1.2.8 sys.objectsAll rows of sys.objects in which the value of the type column is equal to P are related to stored procedures N>sys.procedures
1.2.9 Common Language Runtime (CLR)use sample; EXEC sp_configure ‘clr_enabled’,1 RECONFIGURE N> You enable and disable the use of CLR through the clr_enabled option of the sp_configure system procedure.
CREATE ASSEMBLYCREATE ASSEMBLY assembly_name [ AUTHORIZATION owner_name ] FROM { dll_file} [WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE }]
AUTHORIZATION
WITH PERMISSION SET
EXTERNAL_ACCESS
SqlProcedure

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
public partial class StoredProcedures {
[SqlProcedure]
public static int GetEmployeeCount() {
int iRows;
SqlConnection conn = new SqlConnection("Context Connection=true");
conn.Open();
SqlCommand sqlCmd = conn.CreateCommand();
sqlCmd.CommandText = "select count(*) as 'Employee Count' " + "from employee";
iRows = (int)sqlCmd.ExecuteScalar(); conn.Close(); return iRows;
 } 
};

csc /target:library GetEmployeeCount.cs /reference:”C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\ MSSQL\Binn\sqlaccess.dll”

USE sample;
GO
CREATE ASSEMBLY GetEmployeeCount FROM 'C:\GetEmployeeCount.dll' WITH PERMISSION_SET = SAFE USE sample;
GO
CREATE PROCEDURE GetEmployeeCount
AS
EXTERNAL NAME GetEmployeeCount.StoredProcedures.GetEmployeeCount
2 Batchesit can be a stored procedure or a user-defined function Routines(Relationship)
2.1 TSQL sequence
2.2 Procedural Extensions
2.2.1 Block of StatementsBEGIN statement_1 statement_2 … END
2.2.2 IF StatementIF executes one Transact-SQL block if a Boolean expression, which follows the keyword IF, evaluates to true.
2.2.3 WHILE StatementThe WHILE statement repeatedly executes one Transact-SQL block while the Boolean expression evaluates to true.
2.2.4 Local VariablesThey are used to store values (of any type) within a batch or a routine. – to be defined using the DECLARE statement. – to be referenced in a batch using the prefix @
DECLARE
prefix @
2.2.5 RETURNit auses the execution of the batch to terminate
2.2.6 RAISEERROR
user-defined error message
@@ERROR
2.2.7 GOTO
2.2.8 WAITFORWAITFOR {DELAY ‘time’ | TIME ‘time’ | TIMEOUT ‘timeout’ }
DELAY
2.2.9 TRY and CATCH Statements
exceptionthe existing problem (raised with the exception) will be relegated to another part of the program
USE sample;
BEGIN TRY BEGIN TRANSACTION
insert into employee values(11111, 'Ann', 'Smith','d2');
insert into employee values(22222, 'Matthew', 'Jones','d4');
-- referential integrity error insert into employee values(33333, 'John', 'Barrimore', 'd2');
COMMIT TRANSACTION
PRINT 'Transaction committed'
END
TRY
BEGIN
CATCH
ROLLBACK
PRINT 'Transaction rolled back' END CATCH

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s