none
The data types varchar and bit are incompatible in the add operator

    Question

  • Hello,

     

    I am trying to write a small dynamic sql statement and I am receiving the following error when working with a bit data type.

     

    The data types varchar and bit are incompatible in the add operator

     

    Here is what I have:

     

    Code Snippet

    create proc [dbo].[usp_SetupDatabaseUser]

    (

    @ProcAccess bit = 0

    )

    as

    begin

    set nocount on

    declare @sql nvarchar(MAX)

     

    -- Check for user existence

    select @sql = 'if (' + @ProcAccess + ' = 1)

    begin

    print ''Hello World''

    end'

    exec @sql

    end

     

     

    After I execute, that is why I receive the error.  Any advice would be appreciated!


    Thanks,

    Flea#

    Monday, February 18, 2008 11:28 PM

Answers

  • Try this:

     

    Code Snippet

    ALTER proc [dbo].[usp_SetupDatabaseUser]

    (

    @ProcAccess bit = 0

    )

    as

    begin

    set nocount on

    declare @sql nvarchar(MAX)

    -- Check for user existence

    select @sql = 'if (' + CAST(@ProcAccess AS CHAR(1)) + ' = 1)

    begin

    print ''Hello World''

     

    end'

    exec sp_executesql @sql

    end

     

     

     

    You need to convert your bit value into a character datatype to concatenate it.  Use exec sp_executesql to execute dynamic sql.

    Monday, February 18, 2008 11:45 PM
    Moderator
  • If you're trying to perform an addition (+) with char and numeric datatypes, SQL doesn't know what you're trying to do - concatenate the character strings with a converted version of the numeric datatype (ex. build a string, which is what you were trying to do), or convert the character elements to a numeric value, and add them together.  It might have been obvious to you in your situation, but the SQL engine doesn't make any assumptions.  Imagine if you did something like this:

     

    Code Snippet

    DECLARE myString varchar(10)

    DECLARE myInt int

     

    SELECT myString = '10'

    SELECT myInt = 100

     

    SELECT (myString  + myInt)

     

     

    What should it return?  110, or 10100?

     

    That's why you need to explicitly cast or convert.

    Tuesday, February 19, 2008 3:28 PM
    Moderator

All replies

  • Try this:

     

    Code Snippet

    ALTER proc [dbo].[usp_SetupDatabaseUser]

    (

    @ProcAccess bit = 0

    )

    as

    begin

    set nocount on

    declare @sql nvarchar(MAX)

    -- Check for user existence

    select @sql = 'if (' + CAST(@ProcAccess AS CHAR(1)) + ' = 1)

    begin

    print ''Hello World''

     

    end'

    exec sp_executesql @sql

    end

     

     

     

    You need to convert your bit value into a character datatype to concatenate it.  Use exec sp_executesql to execute dynamic sql.

    Monday, February 18, 2008 11:45 PM
    Moderator
  • Thank you very much, that is what I needed to do to fix it.  By the way, what's the reasoning behind that error? Why must the bit be cast as a char in this situation?

     

    Thanks,

    Flea#

    Tuesday, February 19, 2008 2:30 PM
  • If you're trying to perform an addition (+) with char and numeric datatypes, SQL doesn't know what you're trying to do - concatenate the character strings with a converted version of the numeric datatype (ex. build a string, which is what you were trying to do), or convert the character elements to a numeric value, and add them together.  It might have been obvious to you in your situation, but the SQL engine doesn't make any assumptions.  Imagine if you did something like this:

     

    Code Snippet

    DECLARE myString varchar(10)

    DECLARE myInt int

     

    SELECT myString = '10'

    SELECT myInt = 100

     

    SELECT (myString  + myInt)

     

     

    What should it return?  110, or 10100?

     

    That's why you need to explicitly cast or convert.

    Tuesday, February 19, 2008 3:28 PM
    Moderator