SQL-CLR - Lesson 1 Playing with SqlMetaData and return OUTPUT values
I am looking through various examples on the internet, sql server 2005 examples and MSDN. I will be sharing on what I have tested on. It is quite fun :) to play with sql clr.
When you build this code below and deploy over sql server 2005, you will find the stored procedure HelloWorld inside Adventureworks\Programmability\Stored Procedures\dbo.HelloWorld.
*I didn't name it usp_HelloWorld since I was just testing it.
Suprisingly, you can't modify this stored procedure inside the SQL Server Management Studio unlike the normal T-Sql Stored Procedures.
In order to execute this, you need to enable CLR Integration inside your sql server 2005. Be default, it is switched off.
How-to enable CLR integration:
- User Interface
http://daron.yondem.com/CategoryView,category,SQL%202005.aspx
- Command Line
http://msdn2.microsoft.com/en-us/library/ms254506(VS.80).aspx
For my 1st lesson, I will be playing with SqlMetaData to create 4 columns and insert values into these columns.
*You can learn this inside SQL Server 2005 examples. I just edit it for my learning purpose.
Check the code samples below:
Hello.cs
using
System;
using
System.Data;
using
System.Data.SqlClient;
using
System.Data.SqlTypes;
using
Microsoft.SqlServer.Server;
namespace
Microsoft.Samples.Learn
{
public sealed partial class Hello
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void HelloWorld(out string outGreeting)
{
// Create 4 columns with specific data types
SqlMetaData nameInfo = new SqlMetaData("Name", SqlDbType.NVarChar, 30);
SqlMetaData addressInfo = new SqlMetaData("Home Address", SqlDbType.NVarChar, 50);
SqlMetaData ageInfo = new SqlMetaData("Age", SqlDbType.Int);
SqlMetaData raceInfo = new SqlMetaData("Race", SqlDbType.NVarChar, 20);
SqlDataRecord newRecord =
new SqlDataRecord(new SqlMetaData[]
{
nameInfo,
addressInfo,
ageInfo,
raceInfo
}
);
// Add 1st record
newRecord.SetString(0, "Chua Wen Ching");
newRecord.SetString(1, "3-5, Bandar Kinrara, Puching");
newRecord.SetValue(2, 21);
newRecord.SetString(3, "Chinese");
// Sends a single row result-set to the client
SqlContext.Pipe.Send(newRecord);
outGreeting = "This is a boring output?";
}
}
}
Test.sql
USE
[AdventureWorks];
DECLARE
@OutputValue NVARCHAR(30);
EXEC
HelloWorld @OutputValue
OUTPUT;
SELECT
@OutputValue
as [The Return Value];
Output in VS 2005
Executing selected script from project script file
Name Home Address Age Race (CS 2.0 removes whitespaces, but it is representing 4 columns)
------------------------------ -------------------------------------------------- ----------- --------------------
Chua Wen Ching 3-5, Bandar Kinrara, Puching 21 Chinese
No rows affected.
(1 row(s) returned)
The Return Value
------------------------------
This is a boring output?
No rows affected.
(1 row(s) returned)
Finished executing selected script from project script file