SQL 2008 DB Dev 70-433 2nd objective Programming Objects Code Crackin #24

Hey Now Everybody,

This is a review of the second objective on the Microsoft exam 70-433 ‘Programming Objects’

While reviewing for the Microsoft exams it’s enjoyable to combine the three sections:
1. Self Paces Book’s chapters content
2. Code, SQL Scripts
3. Concepts from Questions of the Microsoft Training Kit

2 Implementing Programming Objects (16 percent)

2.1 Create & Alter Stored Procedures
2.2 Create & Alter User-Defined Functions UDFs
2.3 Create & Alter DML Triggers
2.4 Create & Alter DDL Triggers
2.5 Create & Deploy CLR-Based Objects c7.2
2.6 Implement Error handling c5.1
2.7 Manage Transactions

Book Chapters Concepts

2.1 Stored Procedures

A stored procedure is a batch of T-SQL code that has a name & stored in db
We can pass parameters to a proc either by name or by position. We can also return data from procs using output params.
We can use the EXECUTE AS clause to cause a proc to execute under a specific security context
Cursors allow us to process data on a row by row basis. However they may not be the most efficient
Try .. Catch blocks provide error handling.

2.2 UDF’s User-Defined Functions
We can create scalar functions, inline table-valued functions & multi –statement table-valued functions.
The function body must be encloded w/in a Begin END block with the exception of inline table-valued functions
Return statement terminates all functions
Functions are not allowed to change the stae of a db or a SQL Server instance.

2.3 DML Triggers – execute when we Add, modify, or remove rows
2.4 DDL Triggers
Triggers are sps that automatically execute in response to DDL or DML events
We can create 3 types of triggers DML, DDL & logon triggers
DML execute when an Insert, update or delete statement occurs
DDL triggers execute when a DDL statement for which the trigger is coded for occurs.
Logon triggers execute when there is a logon attempt
We can access the Interted & deleted tables with a DML Trigger
We can access the XML document provided by the EVENTDATA function w/in a DDL or logon trigger.

2.5 CLR-Based Objects C7.2
SQLCLR must be enabled on the SQL Server Interface when using user-defined objects based on SQLCLR
Objects for development using SQLCLR are UDFs & user-defined aggregates
If we create UDTs based on SQLCLR make sure we test
Filestream can be used when the relevant data mostly involves storing streams larger than a meg (1MB)

2.6 Error Handling
Try Catch blocks
2.7 Manage Transactions

Code –

Stored Procedure Example

USE [ccatto_aspnetdb]  
GO /****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_IsUserInRole] Script Date: 05/11/2010 21:01:07 ******/   
SET ANSI_NULLS ON    
GO 
SET QUOTED_IDENTIFIER< OFF
    
GO    
ALTER PROCEDURE [dbo].[aspnet_UsersInRoles_IsUserInRole]    
@ApplicationName  nvarchar(256)
    , @UserName nvarchar(256)
    , @RoleName nvarchar(256)    
AS    
BEGIN    
DECLARE @ApplicationId uniqueidentifier    
SELECT @ApplicationId = NULL    
SELECT @ApplicationId = ApplicationId 
FROM aspnet_Applications 
WHERE LOWER(@ApplicationName) = LoweredApplicationName     
IF (@ApplicationId IS NULL)    

   RETURN(2)    
DECLARE @UserId uniqueidentifier    
SELECT @UserId = NULL    
DECLARE  @RoleId uniqueidentifier    
SELECT @RoleId = NULL    
   SELECT @UserId  = UserId     
   FROM dbo.aspnet_Users    
   WHERE LoweredUserName  = LOWER(@UserName) 
   AND ApplicationId = @ApplicationId     
IF (@UserId IS NULL) 
    RETURN(2)    
SELECT @RoleId = RoleId     
FROM dbo.aspnet_Roles    
WHERE LoweredRoleName = LOWER(@RoleName) 
    AND ApplicationId = @ApplicationId
IF (@RoleId IS NULL)  
RETURN(3)    
IF (EXISTS( 
    SELECT *
    FROM dbo.aspnet_UsersInRoles 
    WHERE UserId = @UserId 
        AND RoleId = @RoleId ))    
      RETURN(1)    
ELSE    
   RETURN(0)    
END  

Function Generic Example

CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName>
(
-- Add the parameters for the function here
<@param1, sysname, @p1> <Data_Type_For_Param1, , int>,
<@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT 0
)
GO

Trigger Generic example from MSDN library

CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail 'MaryM',
'Don''t forget to print a report for the distributors.'
GO

A few Questions Concepts:

1 Instead of Delete Trigger

2 Create a DDL Trigger to populate a table

3 Rollback & Commit

BEGIN TRANSACTION;
DECLARE @success int;
EXEC @success = spName;
IF @success = 0
ROLLBACK;
ELSE
COMMIT;

4 Alter a trigger –

5 A user-defined function (UDF) can be used directly within a SELECT statement.

6 create a UDF with the SCHEMABINDING option

7 Using Try Catch Blocks

Next Up section three Query Fundamentals.
That is all there will be more,

As always all comments welcome,

Catto