SQLQuery example using a datatable for its parameters

By using the inputdata property on de collection element used by the params argument of the SqlQuery macro, you can construct a dynamic query that uses query parameters.

Smartsite SXML CopyCode image Copy Code
<se:hidden>
    {sql.executenonquery("IF OBJECT_ID('temp_qrydemo') IS NOT NULL DROP TABLE temp_qrydemo", rem="if found, drop table")}
    {sql.executenonquery("CREATE TABLE temp_qrydemo(Nr int PRIMARY KEY IDENTITY, username varchar(max), remark varchar(max))", rem="create table")}
    {sql.executenonquery("INSERT INTO temp_qrydemo(username,remark) values('Me','hero')", rem="fill table")}
</se:hidden>

<h2>Table content</h2>
{datatable.simpleformat(sql.execute("select * from temp_qrydemo", rem="query table"))}

<se:xmldatatable save="qryparams">
    <se:row>
        <se:col name="Name">username</se:col>
        <se:col name="Type">string</se:col>
        <se:col name="Value">Chaca Zulu</se:col>
    </se:row>
    <se:row>
        <se:col>remark</se:col>
        <se:col>string</se:col>
        <se:col>'s avonds</se:col>
    </se:row>
</se:xmldatatable>

<se:format inputdata="qryparams" save="qry">
    <se:rowformat>[{this.field(1)}] = ?:{this.field(1)}</se:rowformat>
    <se:rowdelimiter>,</se:rowdelimiter>
    <se:resultformat>UPDATE temp_qrydemo SET {this.result()} WHERE Nr = 1</se:resultformat>
</se:format>

<h2>The query</h2>
{buffer.get(qry)}

<se:sqlquery sql="{buffer.get(qry)}" resulttype="none">
    <se:parameters>
        <se:parameter name="params">
            <se:collection inputdata="qryparams" />
        </se:parameter>
    </se:parameters>
</se:sqlquery>

<h2>Updated table content</h2>
{datatable.simpleformat(sql.execute("select * from temp_qrydemo", rem="query table"))}

<se:hidden>
    {sql.executenonquery("IF OBJECT_ID('temp_qrydemo') IS NOT NULL DROP TABLE temp_qrydemo", rem="if found, drop table")}
</se:hidden>
Example Result CopyCode image Copy Code
<h2>Table content</h2>
Nr=1; username=Me; remark=hero





<h2>The query</h2>
UPDATE temp_qrydemo SET [username] = ?:username,[remark] = ?:remark WHERE Nr = 1



<h2>Updated table content</h2>
Nr=1; username=Chaca Zulu; remark='s avonds