Error in t-SQL code

Question

Post reply

Error in t-SQL code

river1

SSC-Insane

Points: 24309
More actions
October 30, 2017 at 1:57 am

#324296

Dear all,
I have the following code and I always get an error . it seems to be related witg dates. Can someone help me?
Error:
Msg 402, Level 16, State 1, Line 72
The data types varchar and date are incompatible in the add operator.
Thank you

declare @DB as varchar(50)

declare @SPName Varchar (200)

declare @StartDate date

declare @EndDate date

declare @StrSQL varchar (1000)

set @db ='dwhCore_Prod'

Set @SPname ='[GenericData] - [uspLoadhsatPositionFromPosition]'

set @startdate = '2017-10-21'

set @startdate = '2017-10-23'

set @strSQL = ('

select
COUNT(*) NumberExecutions,
schemaname + '' - '' + SPname StoredProcedure,
convert(varchar(10),StartDateTime,120) StartTime,
convert(varchar(10),EndDateTime,120) EndTime,
(rowsinserted + rowsdeleted + rowsupdated) DataChanges,
DATEDIFF(second, [StartDateTime], [EndDateTime]) TExecutiontimeSec
from
' + @DB + ' .ETL.vwRowLoadLog rll
INNER JOIN
' + @DB + '.Logging.SPExecutions execsp
ON
execsp.ExecutionID=rll.ExecutionID
AND
schemaname + '' - '' + SPname = ' + @SPNAme + '
AND
StartDateTime >= ' + @StartDate + '
AND
enddatetime <= ' + @EndDate + '

GROUP BY

schemaname + '' - '' + SPname, convert(varchar(10),StartDateTime,120)

')

exec (@StrSQL)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Paulo de Jesus Default port Points: 1403 More actions · Answer 1

The issue is with construction of the dynamic SQL.

If you look at this section as an example:
StartDateTime >= ' + @StartDate + '

StartDataTime >= is of type varchar
but
@StartDate is of type Date

You need to make sure that @StartDate is also a varchar in order to concatenate it with the rest of the SQL script that you are constructing.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Using PRINT instead of EXEC helps debugging these pesky dynamic sql errors:

DECLARE 
 @DB as varchar(50) = 'dwhCore_Prod',
 @SPName Varchar (200) = '[GenericData] - [uspLoadhsatPositionFromPosition]',
 @StartDate date = '2017-10-21',
 @EndDate  date = '2017-10-23',
 @StrSQL varchar (8000)

set @strSQL = ('
SELECT
         COUNT(*) NumberExecutions,
         schemaname + '' - '' + SPname StoredProcedure,
         convert(varchar(10),StartDateTime,120) StartTime,
         convert(varchar(10),EndDateTime,120) EndTime,
         (rowsinserted + rowsdeleted + rowsupdated) DataChanges,
         DATEDIFF(second, [StartDateTime], [EndDateTime]) TExecutiontimeSec
   FROM ' + @DB + '.ETL.vwRowLoadLog rll
   INNER JOIN ' + @DB + '.Logging.SPExecutions execsp
  ON execsp.ExecutionID=rll.ExecutionID
  AND schemaname + '' - '' + SPname = ' + @SPNAme + '
  AND StartDateTime >= ''' + CONVERT(VARCHAR(8),@StartDate,112) + '''
  AND enddatetime <= ''' + CONVERT(VARCHAR(8),@EndDate,112) + '''
   GROUP BY schemaname + '' - '' + SPname, convert(varchar(10),StartDateTime,120)
')

print @StrSQL

--exec (@StrSQL)

You will have to resolve the aggregate errors.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden