Invalid operator for data type. Operator equals add, type equals bit

2008-04-30


When I wrote a sql server stored procedure, got a error message about bit data type when I tried to run this script:

Invalid operator for data type. Operator equals add, type equals bit.

The error msg is saying that a bit type parameter cannot be concatenated against the 2 strings. Cast it as varchar or nvarchar

The final script is like the following:

SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO

IF EXISTS(SELECT name FROM sysobjects WHERE name = 'procADGetStatus4Email' AND type = 'P') BEGIN DROP PROCEDURE dbo.procADGetStatus4Email END GO

/=============================================================== Creation Date: 19 June 2007 Author:        XXXXXX Description:     Get Status List Tables:     StatusDomain Revisions: ===============================================================/ CREATE   PROCEDURE dbo.procADGetStatus4Email @StatusCode    smallint = NULL, @EmailSendInd    bit = NULL AS SET NOCOUNT ON

Declare @strSQL as nvarchar(2000) Declare @strSQLwhere as nvarchar(2000)

select @strSQL = 'select DISTINCT A.StatusCode,  A.Member, A.StatusShortDesc, A.StatusLongDesc,' select @strSQL = @strSQL + 'A.CreatedDate, A.UpdatedDate,  A.UserID, A.EmailSendInd' select @strSQL = @strSQL + ' FROM dbo.StatusDomain A '

select @strSQLwhere = ' WHERE A.StatusCode > 0 '

IF @StatusCode is Not Null and @StatusCode <> '-1' select @strSQLwhere =  @strSQLwhere + ' And (A.StatusCode =  ' + cast(@StatusCode as nvarchar) + ')'

IF @EmailSendInd is Not Null select @strSQLwhere =  @strSQLwhere + ' And (A.EmailSendInd =  ''' + cast(@EmailSendInd as nvarchar) + ''')'

IF DATALENGTH(@strSQLwhere)>0 select @strSQL = @strSQL+ @strSQLwhere

select @strSQL = @strSQL + ' ORDER BY A.StatusCode '

print @strSQL

EXEC  sp_executesql @strSQL

GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO

GRANT  EXECUTE  ON [dbo].[procADGetStatus4Email]  TO [LMS_Readers] GO