using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
namespace SQLSrvLib
{
public class MySP1
{
[SqlProcedure]
public static void MySP()
{
SqlDataRecord rec = new SqlDataRecord(new SqlMetaData("nummer", SqlDbType.Int), new SqlMetaData("navn", SqlDbType.VarChar, 10), new SqlMetaData("dato", SqlDbType.VarChar, 6));
SqlContext.Pipe.SendResultsStart(rec);
using(SqlConnection con = new SqlConnection("context connection=true"))
{
con.Open();
using(SqlCommand sel = new SqlCommand("SELECT nummer, navn, dato FROM split", con))
{
using(SqlDataReader rdr = sel.ExecuteReader())
{
while(rdr.Read())
{
int nummer = (int)rdr["nummer"];
string navn = (string)rdr["navn"];
string dato = (string)rdr["dato"];
foreach(string datopart in dato.Split(' '))
{
rec.SetInt32(0, nummer);
rec.SetString(1, navn);
rec.SetString(2, datopart);
SqlContext.Pipe.SendResultsRow(rec);
}
}
}
}
}
SqlContext.Pipe.SendResultsEnd();
}
}
}
1>
2>
3> CREATE ASSEMBLY MySP1 FROM 'C:\Work\clrsp\MySP1.dll' WITH PERMISSION_SET = SAFE;
4> GO
1> CREATE PROCEDURE MySP
2> AS
3> EXTERNAL NAME MySP1.[SQLSrvLib.MySP1].MySP;
4> GO
1> CREATE TABLE split (
2> nummer INTEGER,
3> navn VARCHAR(10),
4> dato VARCHAR(32),
5> PRIMARY KEY(nummer)
6> )
7> GO
1> INSERT INTO split VALUES(1, 'Hans', 'RH 100521 - 120521')
2> INSERT INTO split VALUES(2, 'Per', 'RH 100521 - 120521')
3> INSERT INTO split VALUES(3, 'Hans', 'RO 100521')
4> INSERT INTO split VALUES(4, 'Pia', 'SLA 100520 - 120520')
5> GO
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
1> SELECT * FROM split
2> GO
nummer navn dato
----------- ---------- --------------------------------
1 Hans RH 100521 - 120521
2 Per RH 100521 - 120521
3 Hans RO 100521
4 Pia SLA 100520 - 120520
(4 rows affected)
1> EXEC MySP
2> GO
nummer navn dato
----------- ---------- ------
1 Hans RH
1 Hans 100521
1 Hans -
1 Hans 120521
2 Per RH
2 Per 100521
2 Per -
2 Per 120521
3 Hans RO
3 Hans 100521
4 Pia SLA
4 Pia 100520
4 Pia -
4 Pia 120520
(14 rows affected)
1> DROP TABLE split
2> GO
1> DROP PROCEDURE MySP
2> GO
1> DROP ASSEMBLY MySP1
2> GO