stored procedure problem - SQL Server Forums

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

SQL Server Development (2000)

stored procedure problem

Author

Topic

vandalesm
Starting Member

8 Posts

Posted - 2004-10-13 : 21:24:22

hi to you all. this is my first post here. ive got this weird problem in my stored procedure. i have run the stored procedure in query analyzer and it takes too long to complete with not patience i had to stop the query. but when i take the content of the stored procedure and run it in query analyzer it just takes 5 seconds to complete. why this is happening. im puzzled any help will greatly appriciated. thanks.

vandalesm

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-10-13 : 22:05:25

If you post the stored procedure it would help us. Have you tried to recompile the query? EXEC sp_recompile 'queryname'

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

vandalesm
Starting Member

8 Posts

Posted - 2004-10-13 : 22:26:42

here is my stored proc

----------------------------
CREATE Procedure myStoreProc
@gc as varchar(50),
@f as varchar(50),
@sy as varchar(50)

As
Set Nocount On

if @f <> ''
set @f = rtrim(ltrim(@f)) + '%'
else
set @f = '%'

declare @gc_l as varchar(20)
declare @gc as varchar(20)

set @gc=@gc
set @gc_l = @gc + '-L'
set @gc = @gc + '-N'

Declare @ctr as int

--check if there is record found
Set @ctr = (select count(*) from table1 where fID like @f and gcUMN=@gc)
IF @ctr < 1
BEGIN
select 2 as Status, 'No records found!' as Msg
Return
END

declare @count int, @error_var int, @rowcount_var int
set @count=0
set @error_var=0
set @rowcount_var=0

begin tran

update sts2003.dbo.student_gs set g_gentry=b.g_gentry
from sts2003.dbo.student_gs a inner join table1 b
on a.bio_recid=b.bio_recid and a.c_crsid=b.c_crsid and a.g_year=b.g_year and a.gcumn=b.gcumn
where b.fid like @f and b.g_year=@sy and (a.gcumn=@gc + '-N' or a.gcumn=@gc + '-COM' or a.gcumn=@gc + '-CON')
select @error_var=@@error, @rowcount_var=@@rowcount
if @error_var <> 0
begin
rollback tran
select '1' as Status, 'Error' as Msg
return
end
set @count = @rowcount_var

update sts2003.dbo.student_gs set g_gentry=case when isnumeric(a.G_gENTRY)='1' then (select GR_g from tblgScale where GR_SCALETO >= a.G_gENTRY AND GR_SCALEFR <= a.G_gENTRY) else a.G_gENTRY end
from sts2003.dbo.student_gs b inner join table1 a on a.bio_recid=b.bio_recid and a.c_crsid=b.c_crsid and a.g_year=a.g_year
where a.fid like @f and a.g_year=@sy and a.gcumn=@gc + '-N'
and b.gcumn=@gc + '-L'
select @error_var=@@error, @rowcount_var=@@rowcount
if @error_var <> 0
begin
rollback tran
select '1' as Status, 'Error' as Msg
return
end
set @count = @count + @rowcount_var

insert into sts2003.dbo.student_gs(C_CRSID, BIO_RECID, G_YEAR, gcUMN, G_gENTRY, G_DATEENTRY,G_TIMEENTRY,G_gLEVEL,G_DTCOMPLETE,G_SCHOOLID)
select a.C_CRSID, a.BIO_RECID, a.G_YEAR, a.gcUMN, a.G_gENTRY, a.G_DATEENTRY, a.G_TIMEENTRY, a.G_gLEVEL, a.G_DTCOMPLETE, a.G_SCHOOLID
from table1 a left outer join sts2003.dbo.student_gs b on b.bio_recid=a.bio_recid and b.c_crsid=a.c_crsid and b.g_year=a.g_year
where a.fid like @f and a.g_year=@sy and b.gcumn is null
and (a.gcumn=@gc + '-N' or a.gcumn=@gc + '-COM' or a.gcumn=@gc + '-CON')
union
select a.C_CRSID, a.BIO_RECID, a.G_YEAR, @gc + '-L',
G_g=case when isnumeric(a.G_gENTRY)='1' then (select GR_g from tblgScale where GR_SCALETO >= a.G_gENTRY AND GR_SCALEFR <= a.G_gENTRY) else a.G_gENTRY end,
a.G_DATEENTRY, a.G_TIMEENTRY, a.G_gLEVEL, a.G_DTCOMPLETE, a.G_SCHOOLID
from table1 a
left outer join sts2003.dbo.student_gs b on b.bio_recid=a.bio_recid and b.c_crsid=a.c_crsid and b.g_year=a.g_year
where a.fid like @f and a.g_year=@sy and a.gcumn=@gc and b.gcumn is null
order by a.bio_recid

select @error_var=@@error, @rowcount_var=@@rowcount
if @error_var <> 0
begin
rollback tran
select '1' as Status, 'Error' as Msg
return
end
set @count=@count+@rowcount_var
commit tran

select '0' as Status,'Successfully exported' as Msg, @count as Export_Count

Set Nocount Off
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources