Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Generic Content Replacement query

Author  Topic 

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2014-09-12 : 07:24:52
For a customer I'm going to make a POC to move a relative small database from on-premises to MS Azure SQL DB.

One thing that is getting in the way is this: I have a CLR SP in de DB that cannot be migrated since it's CLR aka .NET code running on the server.

What this CLR SP Does is this : I feed it with a Content string (Email / Letter) and a SQL Query with two parameters (PK and helping key).

In the content string I have something like "Hello mister ##PersonName## your course ##CourseName## is starting ##CourseDate##"

The query is a query that will return a certain amount of columns and only 1 record.

For each column I do a replacement AKA

Content = Replace (Content,## + NAMEOFCOLUMN + ##, VALUEOFCOLUMN)

so I get a perfect merge! In fact I do this twice so I even can have nested parameters
in .NET code this is about 10 lines of simple code, so very powerful and this works like a charm for ages.

I have scratched my head for some time now how I am gonna write this in T-SQL (it's a very DB based solution)

I also look for other options as long as the database can trigger the merge and I don't believe I can make the Azure DB make SOAP calls.

So what thoughts do you have? Though I am lazy and I cannot bill a lot of hours for this, I can write T-SQL pretty easily, so brainfarts are probably good enough!

Maybe I can use temp tables and query them with sys.columns, however, what approach would be clever?






Henri Koppen

~~~~
'Tis better to be silent and be thought a fool, than to speak and remove all doubt.

Abraham Lincoln

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-12 : 08:40:23
Would you a.u.b. post the code to the CLR module? I think it can be translated to a user defined function.
Go to Top of Page

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2014-09-12 : 09:00:01

sqlCom.CommandText = f.GetSQL("39f8ef7d-2e65-dd11-aa49-001e4f3b05fd")

Is just a way to fill the commandtext with a query like : select PersonName, CourseName, CourseDate FROM CoursePersons WHERE CourseGuid = @Key1 AND PersonGuid = @Key2.

If the content is
"Hello mister ##PersonName## your course ##CourseName## is starting ##CourseDate##"


The wanted result is for example:
"Hello mister Koppen your course MCSE is starting 09/12/2014"




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





Henri Koppen

~~~~
'Tis better to be silent and be thought a fool, than to speak and remove all doubt.

Abraham Lincoln
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-12 : 09:28:51
OK, what does f.GetSQL do?

FWIW, The rest looks translatable to a UDF (good thing I can read Dutch!)
Go to Top of Page

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2014-09-12 : 09:50:37
f.GetSQL Is just a function which gets a SQL instruction from a SQLQuery table.

So f.GetSQL("39f8ef7d-2e65-dd11-aa49-001e4f3b05fd") will result in something like

sqlCom.CommandText = "select PersonName, CourseName, CourseDate FROM CoursePersons WHERE CourseGuid = @Key1 AND PersonGuid = @Key2"

However in this case : sqlCom.CommandText = f.GetSQL(SQLGuid.ToString)
It will get the SQL Instruction from the Template (Sjabloon) and uses that. A template is a record in the Template table which consists of a sql instruction, the content of the template and such.
But this is the gist of the function (and the dynamic part) ;

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

Inhoud is dutch for Content.






Henri Koppen

~~~~
'Tis better to be silent and be thought a fool, than to speak and remove all doubt.

Abraham Lincoln
Go to Top of Page

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2014-09-12 : 09:53:44
BTW : I don't see the relation between VB.NET code and UDF. The challange is dynamic replacements based on generic query's. The CLR procedure runs like a charm, but because of moving the Azure SQL I cannot use CLR and doubt if we ever can... I freakin' love CLR and on a database server it runs so very fast!

This construct is very powerful. FYI I also posted this on oDesk.com and am willing to pay for a solution...

Henri Koppen

~~~~
'Tis better to be silent and be thought a fool, than to speak and remove all doubt.

Abraham Lincoln
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-12 : 10:01:15
"Inhoud is dutch for Content." -- weet ik!

So, the UDF will get a query from the Template table then execute it. So, in the UDF, that section becomes basically

DECLARE @sql nvarchar(4000)
set @sql = (select <column containing sql command> from <template table>)
exec sp_executesql @sql

It's not hard to build up list of columns and query arguments. There's a great article on that kind of thing here:

sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Go to Top of Page

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2014-09-12 : 10:48:05
I am sorry Gerald, it's a bit more than building up a query. Maybe my description is not clear enough since I get a lot for return question on oDesk too.
As I see it now, there's no easy solution to my problem and I have to rewrite some database logic into my webservice. What I want is not easily accomplished in SQL, so have to take a different tactic.


Henri Koppen

~~~~
'Tis better to be silent and be thought a fool, than to speak and remove all doubt.

Abraham Lincoln
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-12 : 12:51:40
Post the getsql function. I'll convert it to a udf. Then, we can use it to code up the main function as a udf
Go to Top of Page

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2014-09-15 : 05:25:30
Gerald,


GETSQL just get's SQL instruction from a source. f.GetSQL (Some guid) will typically return a query like :

SELECT TOP 1
dbo.fVandaag(GETDATE()) AS Vandaag
, CAST (YEAR(GETDATE()) AS VARCHAR) + CAST (p.Volgnummer AS VARCHAR) AS [CertificaatNummer]
, CASE WHEN r.Naam LIKE ''%GHN%'' THEN NULL ELSE r.Naam END AS [RelatieNaam] -- Relatie naam van instructeur
, CASE WHEN p.Straat IS NULL THEN r.PostStraat ELSE p.Straat END AS [PostStraat]
, CASE WHEN p.Straat IS NULL THEN r.PostHuisnummer ELSE p.Huisnummer END AS PostHuisnummer
, CASE WHEN p.Straat IS NULL THEN r.PostPostcode ELSE p.Postcode END AS PostPostcode
, CASE WHEN p.Straat IS NULL THEN r.PostPlaats ELSE p.Plaats END AS PostPlaats
, ISNULL (p.Roepnaam, p.NaamVoluit) AS InstructeurVoornaam
, p.NaamVoluit AS InstructeurNaam
, p.Voorletters AS InstructeurVoorletters
, p.Achternaam AS IntructeurAchternaam
, CONVERT (VARCHAR, p.Geboortedatum, 105) AS GeboorteDatum
, P.Geboorteplaats AS [GeboortePlaats]
, p.Geslacht
, P.BSN
, P.NIBHVnummer
, p.EHBOnummer
FROM Personen p
LEFT JOIN Relaties r ON r.Guid = p.RelatieGuid -- Let op ! Relatie van Trainer!
WHERE p.Verwijderd=0
AND p.Guid = @Guid


This query will produce 1 record with some columns which is used to replace a text like this (Templatecontent)


Utrecht, ##Vandaag##

Beste ##InstructeurNaam## ,

Hierbij wil ik u vragen of u de volgende training(en) voor ons zou kunnen verzorgen:

##HTML##

Ik zie uw reactie met belangstelling tegemoet.

Met vriendelijke groet,

##PostPlaats## ,

GHN Trainingen


- - - -

On oDesk someone proposed a very straight forward stored procedure, but as of today, no easy solution is possible.




Henri Koppen

~~~~
'Tis better to be silent and be thought a fool, than to speak and remove all doubt.

Abraham Lincoln
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-15 : 08:05:49
OK.so translate that function to a UDF, then we can translate the main function and stop using the clr
Go to Top of Page

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2014-09-29 : 05:23:52
I found a brilliant freelancer on oDesk who made it for me. Here's the solution to my problem. What he did, and I didn't think of is making XML of the output so you can iterate through the nodes.

I paid for the solution, but you don't have to






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





Henri Koppen

~~~~
'Tis better to be silent and be thought a fool, than to speak and remove all doubt.

Abraham Lincoln
Go to Top of Page
   

- Advertisement -