You must complete the following steps:
1. Create an assembly that contains the stored procedure method.
2. Register the assembly with SQL Server.
3. Create a stored procedure based on the assembly.
Creating the Stored Procedure Assembly
File: RandomRows.cs
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public class RandomRows
{
[SqlProcedure]
public static void GetRandomRow()
{
SqlDataAdapter dad = new SqlDataAdapter("SELECT Id,Title FROM Products", "context connection=true");
DataTable dtblProducts = new DataTable();
dad.Fill(dtblProducts);
Random rnd = new Random();
DataRow ranRow = dtblProducts.Rows[rnd.Next(dtblProducts.Rows.Count)];
SqlDataRecord result = new SqlDataRecord(new SqlMetaData("Id", SqlDbType.Int), new SqlMetaData("Title", SqlDbType.NVarChar, 100));
result.SetSqlInt32(0, (int)ranRow["Id"]);
result.SetSqlString(1, (string)ranRow["Title"]);
SqlContext.Pipe.Send(result);
}
[SqlProcedure]
public static void GetRandomRows(int rowsToReturn)
{
SqlDataAdapter dad = new SqlDataAdapter("SELECT Id,Title FROM Products", "context connection=true");
DataTable dtblProducts = new DataTable();
dad.Fill(dtblProducts);
SqlDataRecord result = new SqlDataRecord(new SqlMetaData("Id", SqlDbType.Int), new SqlMetaData("Title", SqlDbType.NVarChar, 100));
SqlContext.Pipe.SendResultsStart(result);
Random rnd = new Random();
for (int i = 0; i < rowsToReturn; i++)
{
DataRow ranRow = dtblProducts.Rows[rnd.Next(dtblProducts.Rows.Count)];
result.SetSqlInt32(0, (int)ranRow["Id"]);
result.SetSqlString(1, (string)ranRow["Title"]);
SqlContext.Pipe.SendResultsRow(result);
}
SqlContext.Pipe.SendResultsEnd();
}
}
csc /t:library RandomRows.cs
Registering the Stored Procedure Assembly with SQL Server
CREATE ASSEMBLY RandomRows
FROM 'C:\RandomRows.dll'
To remove the assembly:
DROP Assembly RandomRows
Creating the Stored Procedures
CREATE PROCEDURE GetRandomRow AS
EXTERNAL NAME RandomRows.RandomRows.GetRandomRow
CREATE PROCEDURE GetRandomRows(@rowsToReturn Int) AS
EXTERNAL NAME RandomRows.RandomRows.GetRandomRows
Executing the following command displays three random products from the Products database:
GetRandomRows 3
If you need to delete these stored procedures, you can execute the following two commands:
DROP PROCEDURE GetRandomRow
DROP PROCEDURE GetRandomRows