Invalid operator for data type. Operator equals add, type equals bit
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