Hello. I'm querying SQL Server and need to specify that the value of bit field should be 1. Is it "better" to write the query with a cfqueryparam so that the conversion takes place before SQL Server (assuming CF is even doing that), or just pass 1 and let SQL Server do the conversion? e.g.: Option 1:

WHERE SomeBitCol = <cfqueryparam cfsqltype="bit" value="true">

or Option 2:

WHERE SomeBitCol = 1

(To be clear, this is not a variable. 1/true would be passed as a hard-coded value. If it was a variable, I'd definitely use

<cfqueryparam>

)

Use

qb

and let it solve this for you. 🙂 but I'd always use cfqueryparam, and if you're using it anyway, there's no 'better' with respect to what kind of 'truthy' value it gets

Haha... I'm working on getting

qb

into the stack... but until then,

<cfqueryparam>

it is. 🙂

Personally, I think I'd use option 2 in this case unless someone could convince me there's a good reason to use a param for a non-variable value.

Oh, I'm sorry, I misread that as 'it is' a variable

I'd prefer option 2 as well, fewer working parts.

clearly I need cfcoffeeparam

Thanks, makes sense. In hindsight, I was probably over-analyzing this one.

There is no point in adding parameters for values that will never change. It will work fine but your query plan will be larger than it should be for no good reason.

That makes sense. I think the real question was around conversion to the

BIT

data type in SQL Server. If using

<cfqueryparam>

lightens the DB load `CAST`'ing an integer 0/1 to a

BIT

, then as a general rule it might be better to use

<cfqueryparam>

even on a value that would never change. But it definitely doesn't makes sense because (a) SQL Server would have you write queries with 1/0 so there doesn't seem to be a more efficient way of scripting a direct query to match the

BIT

data type, and (b) like you said, there's the cost of the larger query plan for the minimum (if existent) effort to convert integer 1/0 to bit by the DB.

According to the SQL Server documentation,

BIT

is an integer data type, so a literal 0 or 1 is probably about the best you can do for avoiding type conversion. Using a boolean value would presumably add a conversion rather than avoiding one.

Interesting! Not what I expected. Thanks.