SQL Sever

一、查看存储过程

1.使用sys.sql_modules查看存储过程的定义

例:

select * from sys.sql_modules

2.使用OBJECT_DEFINITION查看存储过程的定义

例:

select OBJECT_DEFINITION(47339233)

3.使用sp_helptext查看存储过程的定义

例:

sp_helptext 'CheckRepli'

 

存储过程: affect_tranches

CREATE PROCEDURE [AFFECT_TRANCHES] AS
IF EXISTS (SELECT 1 FROM T_APPLIS WHERE APPLIS_NOM='SP_EXEC_UPLOG' AND APPLIS_VALEUR='ON')
BEGIN
-- Allocation of new tranches if necessary
EXEC sp_affecte_tranches
-- Allocation of new units for the construction of the lot number if necessary
EXEC sp_affecte_tranches_lot
-- Allocation of new tranches for the construction of the label number if necessary
EXEC sp_affecte_tranches_label
END

存储过程: sp_affecte_tranches

CREATE PROCEDURE [sp_affecte_tranches]
AS
-- script : version : description
-- 1.8.275.0 : 1.0.2.0 : performence improvment

DECLARE @ID integer, @line varchar(50),@family integer, @intervalNeed bigint ,@intervalKeep bigint,@intervalSize bigint,@actualStart bigint,@nostart bigint, @datem varchar(8),@heurem varchar(14),@query varchar(4096)
DECLARE @INTERVAL TABLE ( id integer identity(1,1) primary key,line varchar(50),family integer,intervalNeed bigint,intervalKeep bigint,intervalSize bigint,actualStart bigint)
insert into @INTERVAL(line,family,intervalNeed,intervalKeep,intervalSize,actualStart)
select t.ET_PROD,t.ET_FAMPF,t.TRANCH_NB_AVANCE,RESTE_TRANCHES,t.TRANCH_TAILLE,ACTUAL_START from T_TRANCH t
inner join
(
select distinct ET_PROD,ET_FAMPF,sum(case when TRANCH_NBUSE = 0 then 1 else 0 end) as RESTE_TRANCHES,max(cast(TRANCH_NO_START as bigint)) ACTUAL_START from T_TRANCH
/*where TRANCH_NBUSE=0*/
group by ET_PROD,ET_FAMPF
) as filtered on filtered.ET_PROD = t.ET_PROD and filtered.ET_FAMPF = t.ET_FAMPF and filtered.ACTUAL_START = t.TRANCH_NO_START
where TRANCH_NB_AVANCE > RESTE_TRANCHES


while exists(select 1 from @INTERVAL)
BEGIN
select top 1 @ID = ID,@line=LINE,@family=FAMILY,@intervalNeed=intervalNeed,@intervalKeep=intervalKeep,@intervalSize=intervalSize,@actualStart=actualStart
from @INTERVAL order by ID

SET @datem=convert(varchar,getdate(),112)
SET @heurem=replace(convert(varchar,getdate(),114),':','')
-- Add missing interval
IF @intervalKeep<@intervalNeed
begin
select @nostart=cast(cast(max(cast(t_tranch.tranch_no_start as bigint)+t_tranch.tranch_taille) as bigint) as varchar(20))
from t_tranch where t_tranch.et_fampf=@family

set @query='insert into t_tranch (ET_FAMPF,ET_PROD,TRANCH_NO_START,TRANCH_TAILLE,TRANCH_NB_AVANCE,TRANCH_NBUSE,NOMMODIF,DATEMODIF,HEUREMODIF) values ('''+convert(varchar,@family)+''','''+@line+''','''+convert(varchar,@nostart)+''','''+convert(varchar,@i
ntervalSize)+''','''+convert(varchar,@intervalNeed)+''',''0'',''ASSIGN_TRANCH'','''+@datem+''','''+@heurem+''')'
--print @query
exec sp_exec_and_log_tran @query , 'ASSIGN_TRANCH' , @datem ,@heurem
end

DELETE FROM @INTERVAL where id = @ID
END

 *************************

DECLARE @INTERVAL TABLE ( id integer identity(1,1) primary key,line varchar(50),family integer,intervalNeed bigint,intervalKeep bigint,intervalSize bigint,actualStart bigint)
insert into @INTERVAL(line,family,intervalNeed,intervalKeep,intervalSize,actualStart)

integer identity(1,1) :

int identity (1,1)表示从1开始递增，每次自增1。 表的第一列是id，它是int型的，并且是自增的，也就是你向表中插入数据的时候，不用给id列赋值，id列会自己复制