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

Published on Friday, September 17, 2010

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