SunQuest
           MS SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesMS SQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
  #1  
Old August 29th, 2004, 11:07 PM
Carl Parker Carl Parker is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Nov 2003
Posts: 21 Carl Parker User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
simple question

I am trying to update a stored procedure, but every time I run it I get an error and I can't find the pre-existing stored procedure so that I can delete it and run the new one. This is the error I get.

Create Trigger rlvntdata_bur
Server: Msg 2714, Level 16, State 5, Procedure rlvntdata_bur, Line 65535
There is already an object named 'rlvntdata_bur' in the database.

This is the Stored procedure:

PRINT 'Create Trigger rlvntdata_bur'
go
CREATE TRIGGER rlvntdata_bur
on rlvntdata
for update as
begin
set nocount on

if update(valuetype) or update(value)
begin
if exists(select '1' from inserted where value is null)
begin
update rlvntdata set rlvntdata.dispvalue = null
from inserted i
where i.value is null
and i.valuetype in ('S', 'N', 'D', 'A', 'P')
and i.svrid = rlvntdata.svrid
and i.procid = rlvntdata.procid
and i.rlvntdataseq = rlvntdata.rlvntdataseq
end

if exists(select '1' from inserted where valuetype = 'A')
begin
update rlvntdata set rlvntdata.dispvalue = a.name
from apptmplt a, inserted i
where i.valuetype = 'A'
and i.value is not null
and a.svrid = substring(i.value, 1, 10)
and a.appid = convert(int, substring(i.value, 12, 10))
and i.svrid = rlvntdata.svrid
and i.procid = rlvntdata.procid
and i.rlvntdataseq = rlvntdata.rlvntdataseq
end

if exists(select '1' from inserted where valuetype = 'P')
begin
update rlvntdata set rlvntdata.dispvalue = m.name
from member m, inserted i
where i.valuetype = 'P'
and i.value is not null
and m.memberid = substring(i.value, 4, 10)
and i.svrid = rlvntdata.svrid
and i.procid = rlvntdata.procid
and i.rlvntdataseq = rlvntdata.rlvntdataseq
end

declare @l_svrid varchar(10),
@l_procid int,
@l_rlvntdataseq int,
@l_valuetype char(1),
@l_oldstr varchar(255),
@l_newstr varchar(255),
@l_length_old int,
@l_length_new int,
@l_rlvntdatalist_old varchar(2000),
@l_rlvntdatalist_new varchar(2300)

declare cur_get_rlvntdata cursor local for
select ins.svrid, ins.procid, ins.rlvntdataseq, ins.valuetype
from inserted ins

open cur_get_rlvntdata
while(1=1)
begin
fetch next from cur_get_rlvntdata into @l_svrid, @l_procid, @l_rlvntdataseq, @l_valuetype
if ( @@fetch_status <> 0 ) break

if @l_valuetype in ('S', 'N', 'D')
begin
select @l_oldstr = del.rlvntdataname + '=' + substring(isnull(del.value,''), 1, 100) + '~\_'
from deleted del
where del.svrid = @l_svrid
and del.procid = @l_procid
and del.rlvntdataseq = @l_rlvntdataseq

select @l_newstr = case
when value is not null then ins.rlvntdataname + '=' + substring(ins.value, 1, 100) + '~\_'
when value is null then ''
end
from inserted ins
where ins.svrid = @l_svrid
and ins.procid = @l_procid
and ins.rlvntdataseq = @l_rlvntdataseq
end
else
begin
select @l_oldstr = del.rlvntdataname + '=' + substring(isnull(del.dispvalue,''), 1, 100) + '~\_'
from deleted del
where del.svrid = @l_svrid
and del.procid = @l_procid
and del.rlvntdataseq = @l_rlvntdataseq

select @l_newstr = case
when value is not null then ins.rlvntdataname + '=' + substring(isnull(ins.dispvalue,''), 1, 100) + '~\_'
when value is null then ''
end
from inserted ins
where ins.svrid = @l_svrid
and ins.procid = @l_procid
and ins.rlvntdataseq = @l_rlvntdataseq
end

select @l_rlvntdatalist_old = isnull(rlvntdatalist,'')
from procs
where svrid = @l_svrid
and procid = @l_procid

set @l_length_old = isnull( datalength(@l_rlvntdatalist_old), 0)

set @l_rlvntdatalist_new = replace(isnull(@l_rlvntdatalist_old,''), isnull(@l_oldstr,''), isnull(@l_newstr,''))
set @l_length_new = isnull( datalength(@l_rlvntdatalist_new), 0)

if @l_length_old = @l_length_new
begin
if datalength(@l_oldstr) = datalength(@l_newstr)
begin
update procs
set rlvntdatalist = @l_rlvntdatalist_new
where svrid = @l_svrid
and procid = @l_procid
end
else
begin
if @l_length_old + datalength(@l_newstr) < 2000
begin
update procs
set rlvntdatalist = @l_rlvntdatalist_old + @l_newstr
where svrid = @l_svrid
and procid = @l_procid
end
end
end
else
begin
if @l_length_new < 2000
update procs
set rlvntdatalist = @l_rlvntdatalist_new
where svrid = @l_svrid
and procid = @l_procid
else
update procs
set rlvntdatalist = replace(@l_rlvntdatalist_old, @l_oldstr, '')
where svrid = @l_svrid
and procid = @l_procid
end
end
close cur_get_rlvntdata
end
end
go

Reply With Quote
  #2  
Old August 30th, 2004, 09:53 AM
Carl Parker Carl Parker is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Nov 2003
Posts: 21 Carl Parker User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Never Mind

I figured it out

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesMS SQL Development > simple question


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway