how to rectify this error

[cmrhema]

Hi,

I want to retrieve the values from store_geofence table where the timeduration exceeds certain interval

the procedure is as below

CREATE
 procedure [dbo].[GeofenceByCustomer]
@regno nvarchar(50),
@frmdate nvarchar(50),
@todate nvarchar(50),
@Geofence nvarchar(50),
@interval int,
@userid nvarchar(50)
as
begin
declare @val int
Declare @strSql as nvarchar(3000)
Select @val=count(*) from basestation where superuserid=@userid and
base_station_name=@Geofence
 
--if(@val=0)
begin
        create table #templ(registrationno nvarchar(50),basestation
nvarchar(50),entry_time datetime,exit_time datetime,duration
int)
        select @strSql='
        insert into #templ(registrationno,basestation,entry_time,exit_time,duration)
        select
registrationno,basestation,entry_time,exit_time,datediff(mi,entry_time,exit_time)
as duration from store_geofence where  entry_time>'+@frmdate+' and
exit_time<'+@todate+' and datediff(mi,entry_time,exit_time)>'+@interval+'
and basestation in ('+@Geofence+')  order by entry_time,registrationno'
        execute (@strSql)
        print @strSql
        select * from #templ
        drop table #templ
end
 
end
GO
 

I should specify the duration in int to take affect

datediff(mi,entry_time,exit_time)>'+@interval+'


but i do not know how to rewrite this path
i cannot put as
datediff(mi,entry_time,exit_time)>'+@interval+'


it returns an error
Syntax error converting the nvarchar value '
insert into #templ(registrationno,basestation,entry_time,exit_ time,duration)
select
registrationno,basestation,entry_time,exit_time,da tediff(mi,entry_time,exit_time)
as duration from store_geofence where entry_time>01/01/2008 and
exit_time<01/01/2008 and datediff(mi,entry_time,exit_time)>' to a column of data type int.

How should i rewrite it

please help

regards
cmrhema

[Delerna]

I think the error is actually comming from the date comparison.
There should be singe quotes around them
like this

insert into #templ(registrationno,basestation,entry_time,exit_ time,duration)
select
registrationno,basestation,entry_time,exit_time,da tediff(mi,entry_time,exit_time)
as duration from store_geofence where entry_time>'2008-01-01' and
exit_time<'2008-01-01' and datediff(mi,entry_time,exit_time)> etc

As they are i think they are being read as 1 divided by 1 divided by 2008
when the query sting is executed.



Note, there won't be any entry_time that is both less than and greater than 2008-01-01