(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 (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:
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_modules
system 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.
No comments:
Post a Comment