2779B Module 8 - Implementing Stored Procedures

-A stored procedure is a named Unit of TSQL statements or .NET code.

-Can put input parameters and return output parameter values.

-Can return status value to indicate success or failure.

Mark then shows how to create one (I already know this)

Guidelines:

-Use two part object names inside procedure

-Create several specialized procedures rather than one large one

-Create test and troubleshoot new stored procedure

-do not use sp_ in stored procedure names

-use the same connection settings for all stored procedures

-use temporary stored procedures only as necessary

-a temp stored procedure is created in the tempdb database

Parameterized Stored procedures

Using Input Parameters

-ALTER PROC schema.procname

@MinLength int = 1 –default value

AS

Stuff here

-exec schema.procname ‘5’

-helpful to declare a default value

-validate the data in your input parameters

Using Output Parameters

- ALTER PROC schema.procname

@MinLength int OUTPUT

AS

Stuff here

-exec schema.procname @dept OUTPUT

Execution Plans

Actual or estimated execution of a query

Performance & Optimization

-Table or index scan

-Bookmark lookups

-Filter

-Sort

Viewing Execution Plans

1. display your sql
2. click “display estimated executed plan” or the “include actual execution plan” button
3. Query is parsed or executed depending on the button you clicked
4. Click the execution plan tab.

TSQL

Estimated:

-SET SHOWPLAN_XML ON

-SET SHOWPLAN_TEXT ON

Actual

-SET STATISTICS XML ON

-SET STATISTICS PROFILE ON

Microsoft is gonna kill off the text output, so don’t get used to it.

Caching an execution plan

-sql server maintains a bunch of memory called the procedure cache, lives in RAM so it is fast.

-the query plans may or may not get cached in procedure cache (usually do)

-the way a query is executed depends on it’s execution context (above)

-DDL statements are not typically cached (CREATE ALTER table etc., no reason for keeping in cache)

-Procedure cache eventually fills up in ram, when this happens newer query plans(above) may overwrite older QPs.

Based on Age and Cost… how old is the QP and how much processing time does it cost. The more often used QPs are less likely to be overwritten.

Query Compilation

Queries are compiled by a four step process.

1^st Q is parsed (check syntax)

2^nd then the Q is Normalized

3^rd the Q is Compiled

4^th Optimized

-If you squirt a bunch of SQL at the DB Engine it goes through this whole process before it executes the Q.

-If you create a stored procedure it goes through these four steps in the process of CREATE Procedure and then caches the results for later use when the stored procedure is called.

Forcing Recompilation

1. sp_recompile
2. WITH RECOMPILE at creation (this recompiles the stored procedure ever time it is run)
3. WITH RECOMPILE on execution (recompiles only at that specific execution)

Why do this?

The QP is very much dependant on the indexes available, if you change the indexes the QP may become less efficient. You would want to recompile the QP to create a new more efficient QP.

Execution Plans

Mark breezed by this without much explanation. (read chapter for more detail)

Starts on page 8-19 (read the chapter it didn’t go into detail either) I know that table scans are bad on large tables, I wonder what other things I am supposed to be looking for here.

In the review Mark reviews stored procedures but he also reviews functions which isn’t covered until the next chapter. Baaaad mark.

end of module quiz

The quiz asks a question about “execute as” Mark didn’t go over this in the video…

Run this (each line individually) and it will make more sense:

SELECT SUSER_NAME(), USER_NAME();

EXECUTE AS USER = 'claus';

REVERT

SELECT SUSER_NAME(), USER_NAME();

This is from books online:

A. Using EXECUTE AS and REVERT to switch context

The following example creates a context execution stack using multiple principals. The REVERT statement is then used to reset the execution context to the previous caller. The REVERT statement is executed multiple times moving up the stack until the execution context is set to the original caller.

Copy Code

USE AdventureWorks;

GO

--Create two temporary principals

CREATE LOGIN login1 WITH PASSWORD = 'J345#$)thb';

CREATE LOGIN login2 WITH PASSWORD = 'Uor80$23b';

GO

CREATE USER user1 FOR LOGIN login1;

CREATE USER user2 FOR LOGIN login2;

GO

--Give IMPERSONATE permissions on user2 to user1

--so that user1 can successfully set the execution context to user2.

GRANT IMPERSONATE ON USER:: user2 TO user1;

GO

--Display current execution context.

SELECT SUSER_NAME(), USER_NAME();

-- Set the execution context to login1.

EXECUTE AS LOGIN = 'login1';

--Verify the execution context is now login1.

SELECT SUSER_NAME(), USER_NAME();

--Login1 sets the execution context to login2.

EXECUTE AS USER = 'user2';

--Display current execution context.

SELECT SUSER_NAME(), USER_NAME();

-- The execution context stack now has three principals: the originating caller, login1 and login2.

--The following REVERT statements will reset the execution context to the previous context.

REVERT;

--Display current execution context.

SELECT SUSER_NAME(), USER_NAME();

REVERT;

--Display current execution context.

SELECT SUSER_NAME(), USER_NAME();

--Remove temporary principals.

DROP LOGIN login1;

DROP LOGIN login2;

DROP USER user1;

DROP USER user2;

GO

--

Syntax

{ EXEC | EXECUTE ] AS <context_specification>

[;]

<context_specification>::=

{ LOGIN | USER } = 'name'

[ WITH { NO REVERT | COOKIE INTO @varbinary_variable } ]

| CALLER