what should be modified in this stored procedure

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 2005 Forums

Transact-SQL (2005)

what should be modified in this stored procedure

Author

Topic

cmrhema
Starting Member

20 Posts

Posted - 2008-04-13 : 11:16:08

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,datediff(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

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-13 : 11:20:29

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

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-13 : 11:31:16

i dont think you really need to use dynamic sql here. Can you try removing the dynamic sql like this & try:-

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)

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  ','+ @Geofence + ',' like '%,' + basestation + ',%'
order by entry_time,registrationno

select * from #templ
drop table #templ
end

end
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources