Hallo,
ich bin ganz frisch hier im Forum und habe auch direkt schon eine Frage...
Ich habe folgende Procedure ( MS SQL 2005 ) die ich mal zur Diskussion stellen möchte.
Es geht mir hier um prinzipielle "not to do´s"
Nicht erschrecken.... soll nur die bisherige Struktur verdeutlichen...
bin über jede Anregung froh...
Gruß MD
ich bin ganz frisch hier im Forum und habe auch direkt schon eine Frage...
Ich habe folgende Procedure ( MS SQL 2005 ) die ich mal zur Diskussion stellen möchte.
Es geht mir hier um prinzipielle "not to do´s"
Nicht erschrecken.... soll nur die bisherige Struktur verdeutlichen...
bin über jede Anregung froh...
Code:
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[sp_st_wT] @wt int AS declare @1 int declare @lfd_nr int declare @position int declare @last_lfd_nr int declare @st1 int DECLARE @aktive int set @aktive=1 if @aktive=1 BEGIN SET NOCOUNT ON if @wt=1 BEGIN select @last_lfd_nr=(select MAX(station.Lfd_Nr) from station Where (station.string30 NOT LIKE N'')) set @lfd_nr=1 WHILE @lfd_nr <= (@last_lfd_nr) BEGIN select @position=(select number30 from station where lfd_nr=@lfd_nr) UPDATE station SET -- QIDNr station.number30 = @position+20 where station.Lfd_Nr=(@lfd_nr) SET @lfd_nr=@lfd_nr+1 END END ELSE BEGIN PRINT @wt END DECLARE @count_linie int declare @rg varchar (20) declare @rg_exists int declare @rg_status int declare @pk varchar (20) declare @pk_exists int declare @pk_status int declare @ext varchar (20) declare @ext_exists int declare @ext_status int declare @kue varchar (20) declare @kue_exists int declare @kue_status int declare @sd varchar (20) declare @sd_exists int declare @sd_status int declare @pp varchar (20) declare @pp_exists int declare @pp_status int declare @rm varchar (20) declare @rm_exists int declare @rm_status int select @pk=(select string30 from station where lfd_nr=(select MAX(lfd_nr) from station where number30<=1800 and string30 not like N'')) select @ext=(select string30 from station where lfd_nr=(select MAX(lfd_nr) from station where number30>=1820 and number30<=2800 and string30 not like N'')) select @sd=(select string30 from station where lfd_nr=(select MAX(lfd_nr) from station where number30>=2820 and number30<=3800 and string30 not like N'')) select @kue=(select string30 from station where lfd_nr=(select MAX(lfd_nr) from station where number30>=3810 and number30<=9100 and string30 not like N'')) select @pp=(select string30 from station where lfd_nr=(select MAX(lfd_nr) from station where number30>=9100 and number30<=9900 and string30 not like N'')) select @rm=(select string30 from station where lfd_nr=(select MAX(lfd_nr) from station where number30>=10040 and number30<=16400 and string30 not like N'')) -----------------------------erfasse status------------------------------------------------------------- select @pk_status=(select auftrag_status from ku_rohre_im_prozess where qidnr=@pk) select @ext_status=(select auftrag_status from ku_rohre_im_prozess where qidnr=@ext) select @kue_status=(select auftrag_status from ku_rohre_im_prozess where qidnr=@kue) select @sd_status=(select auftrag_status from ku_rohre_im_prozess where qidnr=@sd) select @pp_status=(select auftrag_status from ku_rohre_im_prozess where qidnr=@pp) select @rm_status=(select auftrag_status from ku_rohre_im_prozess where qidnr=@rm) select @pk_exists=(select lfd_nr from kl_20 where qidnr=@pk) if @pk_exists not like N'' print @1 --"bereits erfasst" ELSE begin delete kl_10 where qidnr=@pk if @pk not like N'' insert into kl_20 (qidnr,status) values (@pk,@pk_status) ELSE print @1 end ------------ ext select @ext_exists=(select lfd_nr from kl_30 where qidnr=@ext) if @ext_exists not like N'' print @1 -- "bereits erfasst" ELSE begin delete kl_20 where qidnr=@ext delete kl_30 where qidnr=@ext if @ext not like N'' insert into kl_30 (qidnr,status) values (@ext,@ext_status) ELSE print @1 -- "leer" end ------------ sd select @sd_exists=(select lfd_nr from kl_40 where qidnr=@sd) if @sd_exists not like N'' print @1 -- "bereits erfasst" ELSE begin delete kl_30 where qidnr=@sd delete kl_40 where qidnr=@sd if @sd not like N'' insert into kl_40 (qidnr,status) values (@sd,@sd_status) ELSE print @1 -- "leer" end ------------ kue select @kue_exists=(select lfd_nr from kl_50 where qidnr=@kue) if @kue_exists not like N'' print @1 -- "bereits erfasst" ELSE begin delete kl_40 where qidnr=@kue delete kl_50 where qidnr=@kue if @kue not like N'' BEGIN insert into kl_50 (qidnr,status) values (@kue,@kue_status) --insert into kl_protokoll (qidnr,bemerk) values (@kue,'insert_ku') END ELSE print @1 -- "kue_ leer" end --DEAKTIVIERT TAKT RM ------------ pp select @pp_exists=(select lfd_nr from kl_60 where qidnr=@pp) if @pp_exists not like N'' print @1 -- "bereits erfasst" ELSE begin delete kl_50 where qidnr=@pp delete kl_60 where qidnr=@pp if @pp not like N'' insert into kl_60 (qidnr,status) values (@pp,@pp_status) ELSE print @1 -- "leer" end ---DEAKTIVIERT TAKTRM ------------ rm select @rm_exists=(select lfd_nr from kl_70 where qidnr=@rm) if @rm_exists not like N'' begin delete kl_60 where qidnr=@rm print @1 -- "bereits erfasst" end ELSE begin if @rm not like N'' insert into kl_70 (qidnr,status) values (@rm,@rm_status) ELSE print @1 -- "leer" end SET NOCOUNT OFF END ELSE print @1 -- "DEAKTIVIERT"
Gruß MD
Comment