Implement Programming Objects: Design and implement stored procedures

(This is part of the Study Guide series, 70-464)
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:
use MeasureMySkills;
go
create proc dbo.GetCurrentDateTime
as
       select concat(‘Current TimeStamp: ‘, current_timestamp);
go
exec dbo.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:
use MeasureMySkills;
go
alter proc dbo.GetCurrentDateTime
as
       select concat(‘Current TimeStamp: ‘, current_timestamp);
       return @@error;
go
declare @return_result int;
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:
use MeasureMySkills;
go
alter proc dbo.GetCurrentDateTime
       @string1 nvarchar(128),
       @stringOutput nvarchar(128) output
as
       set @stringOutput = concat(‘String1 Value: ‘, @string1)
go
declare @procOutput nvarchar(128);
exec dbo.GetCurrentDateTime ‘My String1’, @procOutput output;
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;
public class FileSystemProcs
{
    [SqlProcedure]
    public static void GetFixedDrives()
    {
        DriveInfo[] logicalDrives = DriveInfo.GetDrives();
        SqlDataRecord OutputData = new SqlDataRecord(
                new SqlMetaData(“drive_name”, SqlDbType.NVarChar, 10),
                new SqlMetaData(“available_space_mb”, SqlDbType.BigInt)
            );
        SqlContext.Pipe.SendResultsStart(OutputData);
        foreach (DriveInfodi 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:
use MeasureMySkills;
go
alter database MeasureMySkills
set trustworthy on;
go
create assembly FileSystemProcs
from ‘C:\SqlServer\SQLCLR\FileSystem\FileSystem\bin\Release\FileSystem.dll’
with permission_set = unsafe;
go
create proc GetFixedDrives
as external name FileSystemProcs.FileSystemProcs.GetFixedDrives;
go
exec GetFixedDrives;
go
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:
use MeasureMySkills;
go
create type NameNumber
as table
(
       Name nvarchar(100),
       Number int
);
go
create proc NameNumberSelect
       @MyTable NameNumber readonly
as
       select
              Name,
              Number + 10
       from @MyTable;
go
declare @TblInput as NameNumber;
insert into @TblInput
values
(
       ‘Eleven’,
       11
),
(
       ‘Five’,
       5
);
exec NameNumberSelect @TblInput;
go
Encryption
                When you create a stored procedure, the definition (the CREATE PROCEDURE batch) is stored in the sys.sql_modulessystem catalog view.  The below query will show you the object name and the module definition:
use MeasureMySkills;
go
select
       object_name(object_id) as object_name,
       definition
from sys.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:
create procedure EncryptedProc
with encryption
as
       select 1 as TestValue;
go
select
       object_name(object_id) as object_name,
       definition
from sys.sql_modules
where object_id = object_id(‘EncryptedProc’);
References
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.
Advertisements

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