locked
How to Pass a boolean variable to a SQL query RRS feed

  • Question

  • I have a SSIS variable called MyBoolean defined as type Boolean. I want to pass it to this toy query:

    SELECT 42 where ? <> 0

    so that the value 42 is returned iff ? = 1 -- that is, if the value of MyBoolean is set to True. I see that I can't specify bit (the SQL Server equivalent of a boolean type) in the Parameter mapping tab of the Execute Sql Task editor. What type should I use? I've tried VARIANT_BOOL, SHORT, SIGNED_CHAR and NUMBER, but these don't seem to work. That is, I'm not getting any rows returned when I execute the task with this sort of setup.
    Monday, November 11, 2013 5:38 PM

Answers

  • If you're trying to pass True False etc, you may even pass it as a string and make query like

    SELECT 42 where CAST(? AS bit) <> 0


    And it will still work fine in T-SQL

    • Proposed as answer by Mike Yin Wednesday, November 13, 2013 1:36 PM
    • Marked as answer by Mike Yin Monday, November 18, 2013 2:24 PM
    Monday, November 11, 2013 6:49 PM

All replies

  • There are no Boolean data type in T-SQL. You even do not need it here as you simulate some odd logic.

    Anything non 0 will return 42, so use any SSIS numeric datatype whereas say a DT_INT may work, but I am not sure what the variable maps to.


    Arthur My Blog

    Monday, November 11, 2013 6:29 PM
  • If you're trying to pass True False etc, you may even pass it as a string and make query like

    SELECT 42 where CAST(? AS bit) <> 0


    And it will still work fine in T-SQL

    • Proposed as answer by Mike Yin Wednesday, November 13, 2013 1:36 PM
    • Marked as answer by Mike Yin Monday, November 18, 2013 2:24 PM
    Monday, November 11, 2013 6:49 PM
  • Of course T-SQL has no boolean types, but that was not my question.  I have a boolean type in SSIS that I want to use in some conditional logic in my query when it executes on SQL Server.  So far I've tried the obvious (to me) types,  (DT_INT doesn't seem to be one of the choices in the parameter dialog) but I've yet to get 42 back from any of them.
    Monday, November 11, 2013 9:11 PM
  • That makes sense!

    I'll give that one a try!!

    Monday, November 11, 2013 9:11 PM