Pages

Friday, June 1, 2012

Implement Programming Objects: Design and implement stored procedures

Microsoft’s
Measured Skill description: This objective may include but is not
limited to: create stored procedures and other programmatic objects; techniques
for developing stored procedures; different types of stored procedure results;
create stored procedures for data access layer; analyze and rewrite procedures
and processes; program stored procedures with T-SQL and CLR#; use table-valued
parameters; encryption

What
I see:

·create stored procedures

·stored procedure results

·SQLCLR stored procedures

·table-valued parameters

·encryption

Create Stored Procedures

Creating stored procedures is
done through the CREATE PROC T-SQL statement.Here is a very simplified example of creating
a stored procedure:

useMeasureMySkills;

go

createprocdbo.GetCurrentDateTime

as

selectconcat('Current TimeStamp: ',current_timestamp);

go

execdbo.GetCurrentDateTime;

go

Stored Procedure Results

There are a few ways to output
stored proc results.The first, as seen
in the example above, is through simply SELECTing data to be
output.Depending on your calling medium
(ADO.NET, for example) you can execute the stored procedure and store these
results in a DataTable.Another
way is to actually have a RETURN statement that not only terminates the
stored procedure, but returns a specific value for capture on the calling
entity.This is typically used for
returning procedure success or failure back to the calling code:

useMeasureMySkills;

go

alterprocdbo.GetCurrentDateTime

as

selectconcat('Current TimeStamp: ',current_timestamp);

return@@error;

go

declare@return_resultint;

exec@return_result=dbo.GetCurrentDateTime;

select@return_result;

The last
method is to use an OUTPUT parameter.This allows SQL Server to pass the parameter
and send outputted data back to the calling code:

useMeasureMySkills;

go

alterprocdbo.GetCurrentDateTime

@string1nvarchar(128),

@stringOutputnvarchar(128)output

as

set@stringOutput=concat('String1 Value: ',@string1)

go

declare@procOutputnvarchar(128);

execdbo.GetCurrentDateTime'My String1',@procOutputoutput;

select@procOutput;

SQLCLR Stored Procedures

SQLCLR is big and getting
bigger.It allows the database to
internally house and call managed code through SQL Server’s hosted CLR.There are many benefits to this, as sometimes
it is just easier and more efficient (and in some cases, the only way) to use
managed and compiled code.Below is an
example of this (Note: I am using Visual Studio to write my C# code and compile
it into an assembly, but all you really need is a text editor and the C#
compiler [csc]):

using System;

using Microsoft.SqlServer.Server;

using System.IO;

using System.Data;

publicclassFileSystemProcs

{

[SqlProcedure]

publicstaticvoid GetFixedDrives()

{

DriveInfo[] logicalDrives = DriveInfo.GetDrives();

SqlDataRecord OutputData = newSqlDataRecord(

newSqlMetaData("drive_name", SqlDbType.NVarChar,
10),

newSqlMetaData("available_space_mb", SqlDbType.BigInt)

);

SqlContext.Pipe.SendResultsStart(OutputData);

foreach (DriveInfo
di in logicalDrives)

{

if (di.IsReady)

{

OutputData.SetString(0, di.Name.Substring(0, 1));

OutputData.SetInt64(1, di.AvailableFreeSpace / 1024 / 1024);

SqlContext.Pipe.SendResultsRow(OutputData);

}

}

SqlContext.Pipe.SendResultsEnd();

}

}

Then I
compile the above code into a class library (Note: remember where the DLL is
stored, as we will reference it below).After that, I want to alter the database property TRUSTWORTHY and set it
on, as our stored procedure requires an elevated permission set.Then create the assembly, and the stored
procedure based off of the method:

The above
example is basically a duplicate of the extended stored procedure xp_fixeddrives.But it is a working example of how to use
SQLCLR.It is worth mentioning that
SQLCLR is going to eventually replace extended stored procedures.

Table-Valued Parameters

Table-valued parameters utilize
a user-defined table data type.This
data type can then be specified as a parameter (caveat:Must be READONLY), and passed
to the stored procedure:

useMeasureMySkills;

go

createtypeNameNumber

astable

(

Namenvarchar(100),

Numberint

);

go

createprocNameNumberSelect

@MyTableNameNumberreadonly

as

select

Name,

Number+ 10

from@MyTable;

go

declare@TblInputasNameNumber;

insertinto@TblInput

values

(

'Eleven',

11

),

(

'Five',

5

);

execNameNumberSelect@TblInput;

go

Encryption

When you create a stored
procedure, the definition (the CREATE PROCEDURE batch) is stored in the
sys.sql_modules
system catalog view.The below query
will show you the object name and the module definition:

useMeasureMySkills;

go

select

object_name(object_id)asobject_name,

definition

fromsys.sql_modules;

As you can
see from the above result set, the CREATE PROCEDURE batch is stored in
plain text.If that is the undesired
effect, then you have the option to encrypt the procedure definition.The below example creates an encrypted stored
procedure and then retrieves the name and definition (or lack thereof) to show
how it is stored: