|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
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
|
|||
|
|||
|
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 |
|
#2
|
|||
|
|||
|
Never Mind
I figured it out
|
![]() |
| Viewing: Dev Shed Forums > Databases > MS SQL Development > simple question |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|