• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 675
  • Last Modified:

Varchar and Bit Incompatible??

Experts,

In the attached stored proc, I'm getting this error when I try to compile it:

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

What is this?
USE [Gift_Tracker]
GO
/****** Object:  StoredProcedure [dbo].[sp_GiftNew]    Script Date: 12/10/2007 02:48:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_GiftNew] 
	@GiftName varchar(75)
	,@GiftDescription varchar(256)
	,@GiftYear DateTime
	,@Occasion varchar(2)
	,@Requested bit
AS
	DECLARE @INSERT varchar(1024)

	SET @INSERT = 'INSERT INTO GIFTS '
	SET @INSERT = @INSERT + '(Gift_Name,Gift_Description,Gift_Year,Occasion,Requested) '
	SET @INSERT = @INSERT + 'VALUES(' + @GiftName + ',' + @GiftDescription  
	SET @INSERT = @INSERT + ',' + @GiftYear + ',' + @Occasion 
	SET @INSERT = @INSERT + ',' + @Requested + ')' 
	
	EXEC (@INSERT)

Open in new window

0
NigelRocks
Asked:
NigelRocks
  • 4
5 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Try this one:
USE [Gift_Tracker] 
GO 
/****** Object:  StoredProcedure [dbo].[sp_GiftNew]    Script Date: 12/10/2007 02:48:03 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
ALTER PROCEDURE [dbo].[sp_GiftNew]  
        @GiftName varchar(75) 
        ,@GiftDescription varchar(256) 
        ,@GiftYear DateTime 
        ,@Occasion varchar(2) 
        ,@Requested varchar(5)
AS 
        DECLARE @INSERT varchar(1024) 
 
        SET @INSERT = 'INSERT INTO GIFTS ' 
        SET @INSERT = @INSERT + '(Gift_Name,Gift_Description,Gift_Year,Occasion,Requested) ' 
        SET @INSERT = @INSERT + 'VALUES(' + @GiftName + ',' + @GiftDescription   
        SET @INSERT = @INSERT + ',' + @GiftYear + ',' + @Occasion  
        SET @INSERT = @INSERT + ',' + @Requested + ')'  
         
        EXEC (@INSERT)

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Missed typing the change done..
changed the input datatype of that bit field to varchar(5) to support all possible values like '1', '0', 'True', 'False' and passing any one of the values should work correctly..
0
 
NigelRocksAuthor Commented:
It compiles, but I still dont' know why I can't call a bit a bit.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
It would work if you use the parameters directly..
Since you are preparing the INSERT Statement dynamically, it is converted into varchar..
So you have to use either CAST/ CONVERT bit to varchar in the code or just convert the datatype of that column to varchar instead of bit..

hope this clarifies.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Simplified version of your procedure with bit datatype which would work:
USE [Gift_Tracker]  
GO  
/****** Object:  StoredProcedure [dbo].[sp_GiftNew]    Script Date: 12/10/2007 02:48:03 ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
ALTER PROCEDURE [dbo].[sp_GiftNew]   
        @GiftName varchar(75)  
        ,@GiftDescription varchar(256)  
        ,@GiftYear DateTime  
        ,@Occasion varchar(2)  
        ,@Requested bit
AS  
INSERT INTO GIFTS (Gift_Name,Gift_Description,Gift_Year,Occasion,Requested)
VALUES(@GiftName, @GiftDescription, @GiftYear,@Occasion,@Requested)

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the last suggestion of rrjegan17 is the best method: avoid the dynamic sql.

to explain though the error you get is that @requested needs to be casted into varchar for the @INSERT concatenation:

SET @INSERT = @INSERT + ',' + cast( @Requested as varchar(1)) + ')'

if really you need dynamic sql for another reason, make it this way, to avoid ALL data type issues, AND avoiding quotes issues:



USE [Gift_Tracker]
GO
/****** Object:  StoredProcedure [dbo].[sp_GiftNew]    Script Date: 12/10/2007 02:48:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_GiftNew]
      @GiftName varchar(75)
      ,@GiftDescription varchar(256)
      ,@GiftYear DateTime
      ,@Occasion varchar(2)
      ,@Requested bit
AS
      DECLARE @INSERT nvarchar(1024)

      SET @INSERT = 'INSERT INTO GIFTS '
      SET @INSERT = @INSERT + '(Gift_Name,Gift_Description,Gift_Year,Occasion,Requested) '
      SET @INSERT = @INSERT + 'VALUES(@GiftName, @GiftDescription, @GiftYear ,  @Occasion , @Requested )'
     
      EXEC sp_executesql @INSERT, N' @GiftName varchar(75)
      ,@GiftDescription varchar(256)
      ,@GiftYear DateTime
      ,@Occasion varchar(2)
      ,@Requested bit  '
          , @GiftName ,@GiftDescription ,@GiftYear ,@Occasion  ,@Requested
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now