# Get SUM from MSSQL in Clarion

**URL:** <https://clarionhub.com/t/get-sum-from-mssql-in-clarion/8946>\
**Category:** questions\
**Tags:** mssql, sql\
**Created:** [February 16, 2026, 4:17pm UTC](https://clarionhub.com/t/get-sum-from-mssql-in-clarion/8946 "2026-02-16T16:17:11Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![rob\_bac](https://clarionhub.com/user_avatar/clarionhub.com/rob_bac/32/686_2.png) [@rob\_bac](https://clarionhub.com/u/rob_bac)\
**Post date:** [February 16, 2026, 4:17pm UTC](https://clarionhub.com/t/get-sum-from-mssql-in-clarion/8946/1 "2026-02-16T16:17:11Z")

</div>

Maybe a simple question, but i miss the clue.

I have a MSSQL file, where i want to total a field, between two dates (LOC:DAT1 and LOC:DAT2)  
In SSMS it’s simple:  
SELECT SUM(fil:field) FROM file WHERE (LOC:DAT1\>=fil:date AND fil:date\<=LOC:DAT2)  
this retuns the total sum of fil:field

But what is my Clarion equivalent to get this total?  
Thanks for your help!

Rob

---

<div class="post-metadata">

**Author:** ![KevinErskine](https://clarionhub.com/user_avatar/clarionhub.com/kevinerskine/32/711_2.png) [@KevinErskine](https://clarionhub.com/u/KevinErskine)\
**Post date:** [February 16, 2026, 5:31pm UTC](https://clarionhub.com/t/get-sum-from-mssql-in-clarion/8946/2 "2026-02-16T17:31:05Z")

</div>

check out PROP:SQL and PROP:SQLRowSet

Here is from the help (Not specific to your request)

```clarion
MySQLiteFile{PROP:SQLRowSet} = 'WITH q AS (SELECT COUNT(*) FROM f) SELECT * FROM q'

```

---

<div class="post-metadata">

**Author:** ![seanh](https://clarionhub.com/letter_avatar_proxy/v4/letter/s/4af34b/32.png) [@seanh](https://clarionhub.com/u/seanh)\
**Post date:** [February 17, 2026, 12:09am UTC](https://clarionhub.com/t/get-sum-from-mssql-in-clarion/8946/3 "2026-02-17T00:09:59Z")

</div>

That’s the sort of thing I use a Dummy table for. I have a couple with different columns defined like  
STRING(100)  
REAL  
LONG  
DATE  
etc. The just use Dummy{prop:sql}=‘your sql’.  
The Dummy table needs the /TurboSQL switch: which really means don’t check the columns.  
Note that if the above was the table layout I’d return 2 fields NULL,sum() to put the number into the real.

---

<div class="post-metadata">

**Author:** ![rob\_bac](https://clarionhub.com/user_avatar/clarionhub.com/rob_bac/32/686_2.png) [@rob\_bac](https://clarionhub.com/u/rob_bac)\
**Post date:** [February 17, 2026, 11:45am UTC](https://clarionhub.com/t/get-sum-from-mssql-in-clarion/8946/4 "2026-02-17T11:45:28Z")

</div>

Thanks Sean,

But how will i get the result in a variabele so i can process it?

---

<div class="post-metadata">

**Author:** ![seanh](https://clarionhub.com/letter_avatar_proxy/v4/letter/s/4af34b/32.png) [@seanh](https://clarionhub.com/u/seanh)\
**Post date:** [February 17, 2026, 12:01pm UTC](https://clarionhub.com/t/get-sum-from-mssql-in-clarion/8946/5 "2026-02-17T12:01:53Z")

</div>

Easy. Assuming the fields I put above are a table layout called Dummy with prefix Dum: and the field names are the same as the type.

```auto
Dummy{prop:SQL}='SELECT NULL,SUM(field) FROM file WHERE (LOC:DAT1>=date AND LOC:DAT2<=date)'
NEXT(Dummy)

```

The Sum is now in Dum:Real and you use it as per normal.

---

<div class="post-metadata">

**Author:** ![Mark\_Sarson](https://clarionhub.com/user_avatar/clarionhub.com/mark_sarson/32/6132_2.png) [@Mark\_Sarson](https://clarionhub.com/u/Mark_Sarson)\
**Post date:** [February 17, 2026, 12:16pm UTC](https://clarionhub.com/t/get-sum-from-mssql-in-clarion/8946/6 "2026-02-17T12:16:10Z")

</div>

One Clarion-specific option that hasn’t been mentioned yet, but probably presumed, is /TURBOSQL on the SQL driver.

When /TURBOSQL=TRUE is used, the SQL driver does not validate the table structure at OPEN time and instead matches the result set by ordinal position, not by column name. This allows you to receive results from ad-hoc SQL (including aggregates like SUM()) without defining a VIEW that exactly matches the server schema.

Example:

```clarion
MyResult FILE, DRIVER('MSSQL', '/TURBOSQL=TRUE')
Record RECORD
TotalQty LONG
         END
         END

CODE
OPEN(MyResult)
MyResult{PROP:SQL} = |
  'SELECT SUM(Qty) FROM Products'

```

This works because the driver maps the first column in the result set to the first field in the RECORD buffer.

⚠ Important caveat (from Clarion help):  
/TURBOSQL should only be used for receiving PROP:SQL result sets.  
If you perform any normal Clarion file I/O (GET/PUT/ADD via the driver), you can get corrupted data or GPFs.

So for read-only / aggregate queries, this is often the simplest solution.

Clarion Help reference:  
SQL Driver Strings → TURBOSQL

---

<div class="post-metadata">

**Author:** ![Rick\_UpperPark](https://clarionhub.com/user_avatar/clarionhub.com/rick_upperpark/32/346_2.png) [@Rick\_UpperPark](https://clarionhub.com/u/Rick_UpperPark)\
**Post date:** [February 17, 2026, 2:54pm UTC](https://clarionhub.com/t/get-sum-from-mssql-in-clarion/8946/7 "2026-02-17T14:54:24Z")

</div>

I also need to mention that if you are using /TURBOSQL beware there is a bug when you connect your application to multiple databases. Sometimes the “turboSQL” table will connect to a database different than what the OWNER is set to.  
Most of us are only using a single database in the application so this isn’t any issue, but it is something to be aware of.

---

<div class="post-metadata">

**Author:** ![Rick\_UpperPark](https://clarionhub.com/user_avatar/clarionhub.com/rick_upperpark/32/346_2.png) [@Rick\_UpperPark](https://clarionhub.com/u/Rick_UpperPark)\
**Post date:** [February 17, 2026, 2:57pm UTC](https://clarionhub.com/t/get-sum-from-mssql-in-clarion/8946/8 "2026-02-17T14:57:58Z")

</div>

Another method is to use Prop:Name on a View. See in the Clarion help about view support for aggregate functions and Prop:Name - SQL.

> [@VIEW support for aggregate functions](https://clarionhub.com/t/view-support-for-aggregate-functions/2653):
>
> This is a pretty neat topic from the help. The SQL view engine supports PROP:GroupBy and PROP:Having. These properties allow you to add respectively GROUP BY and HAVING SQL clauses to your SELECT statement. PROP:GroupBy must be set first to allow PROP:Having to be generated. Example: PROGRAM MAP END EMP FILE,DRIVER('ORACLE'),NAME('EMP'),PRE(EMP) P\_EKY\_EMP KEY(EMP:EMPNO),NOCASE,OPT,PRIMARY KEY\_DEP KEY(EMP:DEPTNO),DUP,NOCASE,OPT Record RECORD EMPNO SHORT …

---

<div class="post-metadata">

**Author:** ![jslarve](https://clarionhub.com/letter_avatar_proxy/v4/letter/j/e480ec/32.png) [@jslarve](https://clarionhub.com/u/jslarve)\
**Post date:** [February 17, 2026, 3:08pm UTC](https://clarionhub.com/t/get-sum-from-mssql-in-clarion/8946/9 "2026-02-17T15:08:40Z")

</div>

Another thing to keep in mind, especially if you are writing as a 3rd party, is that some files could have PROP:SQL turned off. [Clarion Object Based Database Drivers Documentation](https://capesoft.com/docs/Driverkit/ClarionObjectBasedDrivers.htm#propsqldisabled)

---

<div class="post-metadata">

**Author:** ![Bruce](https://clarionhub.com/letter_avatar_proxy/v4/letter/b/ccd318/32.png) [@Bruce](https://clarionhub.com/u/Bruce)\
**Post date:** [February 17, 2026, 7:02pm UTC](https://clarionhub.com/t/get-sum-from-mssql-in-clarion/8946/10 "2026-02-17T19:02:03Z")

</div>

Just to be clear; prop:sql turned off, SQL() command turned on.

And to be even more pedantic, _writing_ prop:sql turned off, _reading_ prop:sql remains.

---

<div class="post-metadata">

**Author:** ![rob\_bac](https://clarionhub.com/user_avatar/clarionhub.com/rob_bac/32/686_2.png) [@rob\_bac](https://clarionhub.com/u/rob_bac)\
**Post date:** [February 18, 2026, 12:30pm UTC](https://clarionhub.com/t/get-sum-from-mssql-in-clarion/8946/11 "2026-02-18T12:30:25Z")

</div>

Thansk everybody for the help.  
It’s better now, only a problem rizes when i add the WHERE option.  
In detail the PROP{SQL} is now:

SELECT SUM(FKT\_INK\_BEDR), SUM(FKT\_OMZ\_EX) FROM BAC5300 WHERE (LOC:DAT1\>=FKT\_DATUM AND LOC:DAT2\<=FKT\_DATUM)

But it gives a problem with the two local fields (LOC:DAT1, LOC:DAT2)  
All variabeles are binded, but this error i get now (from the log file):

```plaintext
02A04H(4) 12:16:33.958 Preparing Statement 04F3FE48H : SELECT SUM(FKT_INK_BEDR), SUM(FKT_OMZ_EX) FROM BAC5300 WHERE (LOC:DAT1>=FKT_DATUM AND FKT_DATUM<=LOC:DAT2) Time Taken:0.00 secs
02A04H(4) 12:16:33.958 Executing prepared Statement 04F3FE48H
02A04H(4) 12:16:33.958 Error Occurred: 37000 [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'LOC:'.
[Microsoft][ODBC SQL Server Driver][SQL Server]The label 'LOC' has already been declared. Label names must be unique within a query batch or stored procedure
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
02A04H(4) 12:16:33.958 Time Taken:0.00 secs
02A04H(4) 12:16:33.958 Getting Number of columns for Statement 04F3FE48H Time Taken:0.00 secs
02A04H(4) 12:16:33.958 SET_PROPERTY(RESULT:05A802C8H) Incorrect syntax near 'LOC:'.
The label 'LOC' has already been declared. Label names must be unique within a query batch or stored procedure.
Statement(s) could not be prepared. Time Taken:0.00 secs

```

Any suggestions.  
Thanks  
Rob

---

<div class="post-metadata">

**Author:** ![Bruce](https://clarionhub.com/letter_avatar_proxy/v4/letter/b/ccd318/32.png) [@Bruce](https://clarionhub.com/u/Bruce)\
**Post date:** [February 18, 2026, 12:48pm UTC](https://clarionhub.com/t/get-sum-from-mssql-in-clarion/8946/12 "2026-02-18T12:48:04Z")

</div>

Don’t use colons in the names of variables that you plan to BIND into a filter.  
BIND let’s you set the “bound” name - don’t use a colon in there, or in the WHERE statement.

---

<div class="post-metadata">

**Author:** ![rob\_bac](https://clarionhub.com/user_avatar/clarionhub.com/rob_bac/32/686_2.png) [@rob\_bac](https://clarionhub.com/u/rob_bac)\
**Post date:** [February 18, 2026, 3:24pm UTC](https://clarionhub.com/t/get-sum-from-mssql-in-clarion/8946/13 "2026-02-18T15:24:51Z")

</div>

When using:

SELECT SUM(FKT\_INK\_BEDR), SUM(FKT\_OMZ\_EX) FROM BAC5300 WHERE (LOC\_DAT1\>=FKT\_DATUM AND FKT\_DATUM\<=LOC\_DAT2)

I get the eror:  
Invalid column name ‘LOC\_DAT1’  
Invalid column name ‘LOC\_DAT2’

I’m a bit lost now

---

<div class="post-metadata">

**Author:** ![Bruce](https://clarionhub.com/letter_avatar_proxy/v4/letter/b/ccd318/32.png) [@Bruce](https://clarionhub.com/u/Bruce)\
**Post date:** [February 18, 2026, 6:45pm UTC](https://clarionhub.com/t/get-sum-from-mssql-in-clarion/8946/14 "2026-02-18T18:45:21Z")

</div>

What do your BIND statements look like?

---

<div class="post-metadata">

**Author:** ![JonW](https://clarionhub.com/user_avatar/clarionhub.com/jonw/32/1344_2.png) [@JonW](https://clarionhub.com/u/JonW)\
**Post date:** [February 18, 2026, 8:24pm UTC](https://clarionhub.com/t/get-sum-from-mssql-in-clarion/8946/15 "2026-02-18T20:24:53Z")

</div>

It’s not the colons are the problem, it’s that PROP:SQL sends that string just like it is to the back end, and SQL server doesn’t have a clue what LOC:dat1 or loc\_dat1 are. They are variable names in your clarion program that the server knows nothing about.

If those values are numbers, then you can do:

x{PROP:SQL} = SELECT SUM(FKT\_INK\_BEDR), SUM(FKT\_OMZ\_EX) FROM BAC5300 WHERE (’ & loc:dat1 & '\>= fkt\_datum and fkt\_datum \<= ’ & loc:dat2

If they are strings, then you will need either two single quotes in you PROP:SQL string before and after, or \<39\>.

Also, does MSSQL not have a Between operator? i.e. fkt\_datum between low\_bound and high\_bound?

Bruce will probably point out that this is a good example of a SQL injection vulnerability, which the new drivers (with bound parameters) get rid of.

---

<div class="post-metadata">

**Author:** ![rob\_bac](https://clarionhub.com/user_avatar/clarionhub.com/rob_bac/32/686_2.png) [@rob\_bac](https://clarionhub.com/u/rob_bac)\
**Post date:** [February 19, 2026, 11:24am UTC](https://clarionhub.com/t/get-sum-from-mssql-in-clarion/8946/16 "2026-02-19T11:24:33Z")

</div>

Thank you all!  
Indeed like Jon said, it sends a string and LOC:DAT1 and LOC:DAT2 should not be in the string itself.  
So this is the solution (for users who come to the same problem);  
I define a file:

```clarion
Result FILE,DRIVER('MSSQL', '/TurboSQL=true'),NAME('RESULT'),PRE(RES)
Record RECORD
INKOOP REAL
OMZET REAL
          END
        END

```

Then LOC:DAT1 (begindate) and LOC:DAT2 (enddate) are two Clarion dates, defined as LONG and binded.

To get the information i coded:

```clarion
OPEN(Result)
Result{PROP:SQL}='SELECT SUM(FKT_INK_BEDR), SUM(FKT_OMZ_EX) FROM BAC5300 WHERE (FKT_DATUM BETWEEN '&LOC:DAT1&' AND '&LOC:DAT2&')'
NEXT(Result)
LOC:INK1=RES:INKOOP
LOC:JR1=RES:OMZET
CLOSE(Result)

```

---

<div class="post-metadata">

**Author:** ![Bruce](https://clarionhub.com/letter_avatar_proxy/v4/letter/b/ccd318/32.png) [@Bruce](https://clarionhub.com/u/Bruce)\
**Post date:** [February 19, 2026, 3:29pm UTC](https://clarionhub.com/t/get-sum-from-mssql-in-clarion/8946/17 "2026-02-19T15:29:52Z")

</div>

> [@JonW](#):
>
> Bruce will probably point out that this is a good example of a SQL injection vulnerability,

True, but Clarion programmers in general dont really care much about security. 🙂

---

<div class="post-metadata">

**Author:** ![Bruce](https://clarionhub.com/letter_avatar_proxy/v4/letter/b/ccd318/32.png) [@Bruce](https://clarionhub.com/u/Bruce)\
**Post date:** [February 19, 2026, 3:35pm UTC](https://clarionhub.com/t/get-sum-from-mssql-in-clarion/8946/18 "2026-02-19T15:35:06Z")

</div>

> [@rob\_bac](#):
>
> Indeed like Jon said, it sends a string and LOC:DAT1 and LOC:DAT2 should not be in the string itself.

Sort of. If you BIND the fields then the value should be substituted in the filter before it is sent to the server. (I’ll confirm that tomorrow, its been a while since I last tested that on the old drivers.)

Of course using BIND does not mitigate the security implications; prop:Sql is inherently insecure even if you only use database fields.

---

<div class="post-metadata">

**Author:** ![CarlBarnes](https://clarionhub.com/user_avatar/clarionhub.com/carlbarnes/32/1859_2.png) [@CarlBarnes](https://clarionhub.com/u/CarlBarnes)\
**Post date:** [February 26, 2026, 5:45pm UTC](https://clarionhub.com/t/get-sum-from-mssql-in-clarion/8946/19 "2026-02-26T17:45:40Z")

</div>

Split this topic on February 26, 2026, 5:45pm UTC

A post was merged into an existing topic: [How to avoid SQL injection risks](https://clarionhub.com/t/how-to-avoid-sql-injection-risks/8967)

---

<div class="post-metadata">

**Author:** ![CarlBarnes](https://clarionhub.com/user_avatar/clarionhub.com/carlbarnes/32/1859_2.png) [@CarlBarnes](https://clarionhub.com/u/CarlBarnes)\
**Post date:** [February 26, 2026, 5:44pm UTC](https://clarionhub.com/t/get-sum-from-mssql-in-clarion/8946/20 "2026-02-26T17:44:18Z")

</div>

Split this topic on February 26, 2026, 5:44pm UTC

4 posts were split to a new topic: [How to avoid SQL injection risks](https://clarionhub.com/t/how-to-avoid-sql-injection-risks/8967)