Data Types Varchar and Date incompatible

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Data Types Varchar and Date incompatible

Author

Topic

fralo
Posting Yak Master

161 Posts

Posted - 2013-06-06 : 13:40:35

Hi,

Copied below is my stored procedure but the problem seems to be with the query. I have never really used the '+' operator that much, but I keep getting the error upon compiling.

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

ALTER PROCEDURE [dbo].[specso_Background_Count]
	@StartDate DATE,
	@EndDate   DATE,
	@Department VARCHAR(50),
	@Type VARCHAR(10),
	@Source VARCHAR(10),
	@Verified VARCHAR(10)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    
    DECLARE @where1 varchar(20);
    DECLARE @where2 varchar(20);
    DECLARE @where3 varchar(20);
    DECLARE @where4 varchar(20);

    IF @Department = 'ALL'
       set @where1 = ''
    ELSE
       set @where1 = ' AND dept = ''' + @verified + ''
       
    IF @type = 'ALL'
       set @where2 = ''
    ELSE
       set @where2 = ' AND type = ''' + @type + ''
       
   IF @source = 'ALL'
       set @where3 = ''
    ELSE
       set @where3 = ' AND source = ''' + @source + ''
       
    IF @verified = 'ALL'
       set @where4 = ''
    ELSE
       set @where4 = ' AND verified = ''' + @verified + ''
              
      SELECT sum(count1) as cnt
        FROM [esosql].eso_intranet.[dbo].logDB
       WHERE date2 >= @StartDate and date2 <= @EndDate + @where1 + 
                 @where2 + @where3 + @where4

I thought I could simply concatenate regardless of datatypes. Thanks for any help you could provide.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-06 : 14:07:11

quote:

Originally posted by fralo

Hi,

Copied below is my stored procedure but the problem seems to be with the query. I have never really used the '+' operator that much, but I keep getting the error upon compiling.

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

ALTER PROCEDURE [dbo].[specso_Background_Count]
	@StartDate DATE,
	@EndDate   DATE,
	@Department VARCHAR(50),
	@Type VARCHAR(10),
	@Source VARCHAR(10),
	@Verified VARCHAR(10)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    
    DECLARE @where1 varchar(20);
    DECLARE @where2 varchar(20);
    DECLARE @where3 varchar(20);
    DECLARE @where4 varchar(20);

    IF @Department = 'ALL'
       set @where1 = ''
    ELSE
       set @where1 = ' AND dept = ''' + @verified + ''
       
    IF @type = 'ALL'
       set @where2 = ''
    ELSE
       set @where2 = ' AND type = ''' + @type + ''
       
   IF @source = 'ALL'
       set @where3 = ''
    ELSE
       set @where3 = ' AND source = ''' + @source + ''
       
    IF @verified = 'ALL'
       set @where4 = ''
    ELSE
       set @where4 = ' AND verified = ''' + @verified + ''
              
      SELECT sum(count1) as cnt
        FROM [esosql].eso_intranet.[dbo].logDB
       WHERE date2 >= @StartDate and date2 <= @EndDate + @where1 + 
                 @where2 + @where3 + @where4

I thought I could simply concatenate regardless of datatypes. Thanks for any help you could provide.

You'll need dynamic sql for this purpose. Check sp_executeSQL For reference

By the way, why not simply write your procedure like

If @department = 'all'
set @department=NULL

If @type='all'
set @type=null
..
..

SELECT sum(count1) as cnt
FROM [esosql].eso_intranet.[dbo].logDB
WHERE date2 >= @StartDate and date2 <= @EndDate
AND Dept = ISNULL(@Department,dept)
AND Type = ISNULL(@type,type)
AND Source = ISNULL(@source,Source)
AND Verified = ISNULL(@Verified,Verified)

Cheers
MIK

fralo
Posting Yak Master

161 Posts

Posted - 2013-06-06 : 14:18:14

Like this. I get the same error though.

SET @COMMAND = 'SELECT sum(count1) as cnt
FROM logDB
WHERE date2 >= ''' + @StartDate + ''' and date2 <= ''' + @EndDate + '' +
@where1 + @where2 + @where3 + @where4

EXEC @command

I don't know if I got all my quotes correct.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-06 : 14:50:02

DECLARE @where1 varchar(20),@startDate date,@endDate date, @command varchar(4000)
DECLARE @where2 varchar(20);
DECLARE @where3 varchar(20);
DECLARE @where4 varchar(20);

set @where1=''
SEt @where2=''
set @where3=''
set @where4=''
set @startDate=CONVERT(date,getdate())
set @endDate=CONVERT(date,getdate())

SET @COMMAND = 'SELECT sum(count1) as cnt
FROM logDB
WHERE date2 >= ''' + convert(varchar(10),Convert(date,@StartDate)) + ''' and date2 <= ''' + convert(varchar(10),convert(date,@EndDate)) + '' +
@where1 + @where2 + @where3 + @where4
print @command

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources