CODEWITHSUNDEEP
sqlstored-procedurescoldfusionqoq
Nathaniel
Nathaniel

Reputation: 21

Query of Queries not matching empty string

I have a the output from a stored procedure on a SQL Server that I am running a query of queries on in CF9. I don't have any access to the stored procedure to make any changes, but I can output the contents.

My problem is that my QofQ is matching on several fields, one of which has a record that has an empty string in one of the fields that I'm matching on. The recordset should return that record, but it is not part of my recordset.

<cfquery name="test" dbtype="query">
SELECT * 
FROM dataLimited
WHERE market = <cfqueryparam value="" cfsqltype="cf_sql_varchar">
</cfquery>

This returns zero records, even though I can see the record in the dump of the stored procedure.

Upvotes: 1

Views: 366

Answers (2)

Nathaniel
Nathaniel

Reputation: 21

I'm looping over my QofQ to do subsequent QofQs, creating dynamic rows in a table. So my subsequent QofQs will have WHERE clauses that will match on the (possibly) NULL value or on a Non-NULL value. My solution has been to check the len() of the value and then in the subsequent query use: 

<cfif len(data.market)> 
AND market = <cfqueryparam value="#data.market#" cfsqltype="cf_sql_varchar"> 
<cfelse> 
AND market IS NULL 
</cfif>

Upvotes: 0

Tim Jasko
Tim Jasko

Reputation: 1542

Are you sure that the field has an empty string, and not null? CF does not do a good job of distinguishing between NULL and empty string, but query of queries still respects the difference. i.e. Use the expression:

WHERE market IS NULL

Upvotes: 5

Related Questions