Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server


Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub spMergeSjabloon(ByVal RecordGuid As String, ByVal SjabloonGuid As String, <Runtime.InteropServices.Out()> ByRef Resultaat As String, Optional ByVal RecordGuidOptional As String = "")
        ' Versie 0.01 - 08-08-2008  - Henri Koppen
        ' Beschrijving  :
        ' Pakt een record en een sjabloon, voegt deze samen en geeft het resultaat terug in de vorm van een datatabel

        ' Versie 0.02 - 22-09-2008 - Henri Koppen
        ' Extra Optionele parameter meegenomen RecordGuidOptional deze wordt als @Guid2 meegegeven.
        Dim f As New Functies
        Dim RecordGuid2 As Guid = Guid.Empty

        ' Optionele parameter als GUID doorgeven als het een GUID is.
        If f.IsGuid(RecordGuidOptional) Then
            RecordGuid2 = New Guid(RecordGuidOptional)
        End If

        ' Validatie
        If IsNothing(RecordGuid) OrElse RecordGuid.Length = 0 Then
            Exit Sub
        End If

        If IsNothing(SjabloonGuid) OrElse SjabloonGuid.Length = 0 OrElse Not f.IsGuid(SjabloonGuid) Then
            Exit Sub
        End If

        Dim Inhoud As String
        Dim SQLGuid As Guid


        Dim sqlCon As SqlConnection
        Dim sqlCom As New SqlCommand
        Dim _dt As New DataTable
        Dim _da As SqlClient.SqlDataAdapter

        sqlCom = New SqlCommand
        sqlCon = New SqlConnection("context connection=true;")
        sqlCom.Parameters.Clear()
        sqlCom.CommandText = f.GetSQL("39f8ef7d-2e65-dd11-aa49-001e4f3b05fd")
        sqlCom.Parameters.AddWithValue("@guid", SjabloonGuid)
        sqlCon.Open()
        sqlCom.Connection = sqlCon
        _da = New SqlClient.SqlDataAdapter(sqlCom)
        _da.Fill(_dt)
        sqlCon.Close()

        ' Lege tabel of meerdere records = wegwezen
        If IsNothing(_dt) OrElse _dt.Rows.Count <> 1 Then
            Exit Sub
        End If

        Inhoud = _dt.Rows(0).Item("Inhoud").ToString
        SQLGuid = New Guid(_dt.Rows(0).Item("SqlGuid").ToString)


        If IsNothing(SQLGuid) OrElse SQLGuid = Guid.Empty Then
            Resultaat = Inhoud
            Exit Sub
        End If

        ' We hebben genoeg informatie om een datatable op te halen voor replacement
        _dt = New DataTable
        sqlCom = New SqlCommand
        sqlCon = New SqlConnection("context connection=true;")
        sqlCom.Parameters.Clear()
        sqlCom.CommandText = f.GetSQL(SQLGuid.ToString)
        sqlCom.Parameters.AddWithValue("@guid", RecordGuid)
        sqlCom.Parameters.AddWithValue("@guid2", RecordGuid2) ' Extra optionele guid meegeven
        sqlCon.Open()
        sqlCom.Connection = sqlCon
        _da = New SqlClient.SqlDataAdapter(sqlCom)
        _da.Fill(_dt)
        sqlCon.Close()

        ' Lege tabel of meerdere records = wegwezen
        If IsNothing(_dt) OrElse _dt.Rows.Count <> 1 Then
            Resultaat = Inhoud
            Exit Sub
        End If

        For Each _dtc As DataColumn In _dt.Columns
            Inhoud = Replace(Inhoud, "##" & _dtc.ColumnName.ToString & "##", _dt.Rows(0).Item(_dtc.ColumnName.ToString).ToString)
        Next

        Resultaat = Inhoud

        '' Geef de inhoud terug aan de interface
        'SqlContext.Pipe.Send(Inhoud)



    End Sub
End Class

IF OBJECT_ID('spMergeSjabloon') IS NOT NULL
	DROP PROC spMergeSjabloon
GO
CREATE PROC spMergeSjabloon @query NVARCHAR(MAX), @template NVARCHAR(MAX), @key1 SQL_VARIANT, @key2 SQL_VARIANT = NULL
AS
DECLARE @xmlquery NVARCHAR(MAX)
DECLARE @result NVARCHAR(MAX)
DECLARE @t TABLE(X XML)
DECLARE @xml XML
SET @xmlquery = @query+' FOR XML RAW, ELEMENTS XSINIL, TYPE' --NULL valued column will be an empty element with xsi:null="true"

BEGIN TRY		SET @key1 = CAST(@key1 AS UNIQUEIDENTIFIER)		END TRY BEGIN CATCH END CATCH; --Try to cast anything to GUID
BEGIN TRY		SET @key2 = CAST(@key2 AS UNIQUEIDENTIFIER)		END TRY BEGIN CATCH END CATCH;

INSERT INTO @t
EXEC sp_executesql @xmlquery, @params = N'@guid SQL_VARIANT, @guid2 SQL_VARIANT', @guid=@key1, @guid2=@key2

SELECT @xml = X FROM @t

IF @xml IS NULL
	SET @result = '' --In case of 0 rows the result should be empty
ELSE
BEGIN
	DECLARE @c CURSOR, @name NVARCHAR(MAX), @value NVARCHAR(MAX)
	SET @c = CURSOR FOR
	SELECT	
			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( --handling extreme column names
      			T.C.value('local-name(.)', 'NVARCHAR(MAX)') 
			,'_x0030_','0'),'_x0031_','1'),'_x0032_','2'),'_x0033_','3'),'_x0034_','4'),'_x0035_','5'),'_x0036_','6'),'_x0037_','7'),'_x0038_','8'),'_x0039_','9'),'_x0020_',' '),'_x003C_','<'),'_x003E_','>'),'_x003F_','?'),'_x0025_','%'),'_x0026_','&')
			AS NAME,
			T.C.value('.', 'NVARCHAR(MAX)') AS VALUE
	FROM	@xml.nodes('/row/*') AS T(C)

	SET @result = @template
	OPEN @c
	FETCH NEXT FROM @c INTO @name, @value

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @result = REPLACE(@result, '##'+@name+'##', @value)
		FETCH NEXT FROM @c INTO @name, @value
	END
END
SELECT @result
GO

------------------------
--SAMPLES---------------
------------------------
DECLARE @guid UNIQUEIDENTIFIER
DECLARE @key NVARCHAR(50)
DECLARE @id INT

--Init test data
SELECT @guid = (select top 1 job_id from msdb..sysjobs), @key = (select top 1 cast(job_id as varchar(50)) from msdb..sysjobs), @id = 1

--NULL field values
EXEC spMergeSjabloon 'select * from sys.types where name = @guid', 'max length: ##max_length##, collation name: ''##collation_name##''', 'int'
--No rows
EXEC spMergeSjabloon 'select * from sys.types where name = @guid', 'max length: ##max_length##, collation name: ''##collation_name##''', 'innt'
--Single parameter usage
EXEC spMergeSjabloon 'select name, @Guid [crazy col <>?%&], @Guid2 [2nd parameter] from master.sys.sysdatabases where dbid = @Guid', 'Database name: ##name##, id: ##crazy col <>?%##, 2nd parameter value: ''##2nd parameter##''', 1
--Optional parameter usage
EXEC spMergeSjabloon 'select @Guid2 step, step_name from msdb..sysjobsteps where job_id = @Guid and step_id = @Guid2', 'Name of step ##step## is ''##STEP_NAME##''', @guid, @id
--NVARCHAR AS GUID
EXEC spMergeSjabloon 'select @Guid2 step, step_name from msdb..sysjobsteps where job_id = @Guid and step_id = @Guid2', 'Name of step ##step## is ''##STEP_NAME##''', @key, 2