execute a procedure in another one

• marclas

  Right there with Babe

  Points: 779

  More actions

  August 30, 2012 at 4:53 am

  #260725

  hi i am trying unsuccessfully to execute the following procedure in another one

  1- USE [RECLACSR-DB]

  GO

  /****** Object: StoredProcedure [dbo].[CreerFile] Script Date: 08/30/2012 10:53:30 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  -- =============================================

  -- Author:<Author,,Name>

  -- Create date: <Create Date,,>

  -- Description:<Description,,>

  -- =============================================

  ALTER PROCEDURE [dbo].[CreerFile]

  -- Add the parameters for the stored procedure here

  @filID uniqueidentifier = '00000000-0000-0000-0000-000000000000' OUTPUT,

  @fillNameSend nvarchar (50),

  @filDateSend datetime,

  @filNameReturn nvarchar(50)=null,

  @filDateReturn datetime = null,

  @filSceDest nvarchar (50),

  @filDateRelance datetime = null,

  @filRefRelance nvarchar (50)=null

  AS

  BEGIN

  -- SET NOCOUNT ON added to prevent extra result sets from

  -- interfering with SELECT statements.

  SET NOCOUNT ON;

  IF @filID IS NULL OR @filID = '00000000-0000-0000-0000-000000000000'

  BEGIN

  SET @filID = NEWID()

  END

  -- Insert statements for procedure here

  IF NOT EXISTS (SELECT * FROM dbo.CSR_FILE WHERE FIL_ID = @filID )

  BEGIN

  INSERT INTO dbo.CSR_FILE

  VALUES (@filID ,@fillNameSend ,@filDateSend ,@filNameReturn ,@filDateReturn ,@filSceDest ,@filDateRelance ,@filRefRelance )

  END

  ELSE

  BEGIN

  UPDATE dbo.CSR_FILE

  SET FIL_NAMESEND =@fillNameSend ,FIL_DATESEND = @filDateSend , FIL_NAMERETURN = @filNameReturn ,

  FIL_DATERETURN = @filDateReturn ,FIL_SVCDESTINATAIRE = @filSceDest ,FIL_DATERELANCE = @filDateRelance ,

  FIL_REFRELANCE = @filRefRelance

  WHERE FIL_ID =@filID

  END

  END

  procedure 2

  USE [RECLACSR-DB]

  GO

  /****** Object: StoredProcedure [dbo].[EnregistrerRejet] Script Date: 08/30/2012 09:32:03 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  -- =============================================

  -- Author:<Author,,Name>

  -- Create date: <06082012>

  -- Description:<Description,,>

  -- =============================================

  ALTER PROCEDURE [dbo].[EnregistrerRejet]

  -- Add the parameters for the stored procedure here

  @RejID uniqueidentifier = '00000000-0000-0000-0000-000000000000' OUTPUT,

  @numdde nchar (10),

  @rejetStatut nchar(50)=3,

  @sceorigine nvarchar (1)=null,

  @motifrejet int=1,

  @stadde int=1,

  @comment nvarchar (200)='RAS',

  @Traitement nvarchar (50)='EnregistrerRejet',

  @Destinataire nvarchar (50)=null,

  @Observation nvarchar (200)=null,

  @ObjeRempli nvarchar (200)=null,

  @RefCourrier nvarchar(50)=null

  AS

  BEGIN

  -- SET NOCOUNT ON added to prevent extra result sets from

  -- interfering with SELECT statements.

  SET NOCOUNT ON;

  IF @RejID IS NULL OR @RejID = '00000000-0000-0000-0000-000000000000'

  BEGIN

  SET @RejID = NEWID()

  END

  -- Insert statements for procedure here

  IF NOT EXISTS (SELECT * FROM dbo.CSR_REJET WHERE DOS_NODE = @numdde )

  BEGIN

  INSERT INTO dbo.CSR_REJET (REJ_ID ,DOS_NODE ,REJ_SVCORIG ,MRE_CODE ,REJ_CMT , REJ_STATUT ,REJ_DTREC )

  VALUES (@RejID ,@numdde ,upper (@sceorigine) ,@motifrejet ,@comment , @rejetStatut ,CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS FLOAT)) AS DATETIME) )

  ----MAJ de la table des statut

  update CSR_STATUT set STA_ID = @stadde, STA_DATE = CAST(FLOOR (CAST (CURRENT_TIMESTAMP as float)) as datetime) where DOS_ID = (SELECT dbo.CSR_DOSSIER.DOS_ID

  FROM dbo.CSR_DOSSIER

  WHERE DOS_NODE =@numdde )

  END

  ELSE

  BEGIN

  if (@Traitement ='EnregistrerRejet')

  begin

  UPDATE dbo.CSR_REJET

  SET REJ_SVCORIG =UPPER ( @sceorigine ), MRE_CODE =@motifrejet ,

  REJ_CMT =@comment ,REJ_STATUT = @rejetStatut , REJ_DTREC = CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS FLOAT)) AS DATETIME)

  WHERE DOS_NODE =@numdde

  end

  else if (@Traitement ='Transmettre')

  begin

  update CSR_REJET

  set REJ_SVCDEST =UPPER ( @Destinataire),REJ_OBS = @Observation,REJ_STATUT = @rejetStatut,

  REJ_DATETRT = CAST (FLOOR (CAST (CURRENT_TIMESTAMP as float )) as datetime)

  where DOS_NODE = @numdde

  end

  else if (@Traitement ='Archiver')

  begin

  update CSR_REJET

  set REJ_OBS = @Observation,REJ_STATUT = @rejetStatut,

  REJ_DATETRT = CAST (FLOOR (CAST (CURRENT_TIMESTAMP as float )) as datetime)

  where DOS_NODE = @numdde

  ----MAJ table csr_statut

  update CSR_STATUT

  set STA_ID = @stadde, STA_DATE = CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS FLOAT)) AS DATETIME)

  where DOS_ID = (select DOS_ID from CSR_DOSSIER where DOS_NODE =@numdde )

  end

  else if (@Traitement ='ObjetRempli')

  begin

  update CSR_REJET

  set

  REJ_OBS = @Observation,REJ_OBJREJ = @ObjeRempli,REJ_STATUT = @rejetStatut,

  REJ_DATETRT = CAST (FLOOR (CAST (CURRENT_TIMESTAMP as float )) as datetime)

  where DOS_NODE = @numdde

  ----MAJ table csr_statut

  update CSR_STATUT

  set STA_ID = @stadde, STA_DATE = CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS FLOAT)) AS DATETIME)

  where DOS_ID = (select DOS_ID from CSR_DOSSIER where DOS_NODE =@numdde )

  end

  else if (@Traitement ='Imprimer')

  begin

  update CSR_REJET

  set REJ_DATRANS = CAST (FLOOR (CAST (CURRENT_TIMESTAMP as float )) as datetime),

  REJ_RefCourrier = @RefCourrier

  where DOS_NODE = @numdde and REJ_ID = @RejID

  [highlight=#ffff11]-----table de transmission

  exec dbo.CreerFile '00000000-0000-0000-0000-000000000000',@RefCourrier ,CAST (FLOOR (CAST (CURRENT_TIMESTAMP as float )) as datetime)[/highlight]

  end

  END

  so when i write dbo.CreerFile i always ahave an error "Msg 102, Niveau 15, État 1, Procédure EnregistrerRejet, Ligne 86

  Incorrect syntax near 'FLOOR'."

  thanks for your help

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  August 30, 2012 at 4:58 am

  #1531794

  Replace the line executing the proc with following:

  declare @dt datetime = CAST (FLOOR (CAST (CURRENT_TIMESTAMP as float )) as datetime)

  exec dbo.CreerFile '00000000-0000-0000-0000-000000000000',@RefCourrier , @dt

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• Lokesh Vij

  SSChampion

  Points: 10836

  More actions

  August 30, 2012 at 5:09 am

  #1531797

  Here is the corrected version:

  CREATE PROCEDURE [ENREGISTRERREJET]

  -- Add the parameters for the stored procedure here

  @RejID UNIQUEIDENTIFIER = '00000000-0000-0000-0000-000000000000' OUTPUT,

  @numdde NCHAR (10),

  @rejetStatut NCHAR(50)=3,

  @sceorigine NVARCHAR (1)=NULL,

  @motifrejet INT=1,

  @stadde INT=1,

  @comment NVARCHAR (200)='RAS',

  @Traitement NVARCHAR (50)='EnregistrerRejet',

  @Destinataire NVARCHAR (50)=NULL,

  @Observation NVARCHAR (200)=NULL,

  @ObjeRempli NVARCHAR (200)=NULL,

  @RefCourrier NVARCHAR(50)=NULL

  AS

  BEGIN

  -- SET NOCOUNT ON added to prevent extra result sets from

  -- interfering with SELECT statements.

  SET NOCOUNT ON;

  IF @RejID IS NULL

  OR @RejID = '00000000-0000-0000-0000-000000000000'

  BEGIN

  SET @RejID = NEWID()

  END

  -- Insert statements for procedure here

  IF NOT EXISTS (SELECT *

  FROM dbo.CSR_REJET

  WHERE DOS_NODE = @numdde)

  BEGIN

  INSERT INTO dbo.CSR_REJET

  (REJ_ID,

  DOS_NODE,

  REJ_SVCORIG,

  MRE_CODE,

  REJ_CMT,

  REJ_STATUT,

  REJ_DTREC)

  VALUES (@RejID,

  @numdde,

  UPPER (@sceorigine),

  @motifrejet,

  @comment,

  @rejetStatut,

  CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS FLOAT)) AS DATETIME) )

  ----MAJ de la table des statut

  UPDATE CSR_STATUT

  SET STA_ID = @stadde,

  STA_DATE = CAST(FLOOR (CAST (CURRENT_TIMESTAMP AS FLOAT)) AS DATETIME)

  WHERE DOS_ID = (SELECT dbo.CSR_DOSSIER.DOS_ID

  FROM dbo.CSR_DOSSIER

  WHERE DOS_NODE = @numdde)

  END

  ELSE

  BEGIN

  IF ( @Traitement = 'EnregistrerRejet' )

  BEGIN

  UPDATE dbo.CSR_REJET

  SET REJ_SVCORIG = UPPER (@sceorigine),

  MRE_CODE = @motifrejet,

  REJ_CMT = @comment,

  REJ_STATUT = @rejetStatut,

  REJ_DTREC = CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS FLOAT)) AS DATETIME)

  WHERE DOS_NODE = @numdde

  END

  ELSE

  IF ( @Traitement = 'Transmettre' )

  BEGIN

  UPDATE CSR_REJET

  SET REJ_SVCDEST = UPPER (@Destinataire),

  REJ_OBS = @Observation,

  REJ_STATUT = @rejetStatut,

  REJ_DATETRT = CAST (FLOOR (CAST (CURRENT_TIMESTAMP AS FLOAT)) AS DATETIME)

  WHERE DOS_NODE = @numdde

  END

  ELSE

  IF ( @Traitement = 'Archiver' )

  BEGIN

  UPDATE CSR_REJET

  SET REJ_OBS = @Observation,

  REJ_STATUT = @rejetStatut,

  REJ_DATETRT = CAST (FLOOR (CAST (CURRENT_TIMESTAMP AS FLOAT)) AS DATETIME)

  WHERE DOS_NODE = @numdde

  ----MAJ table csr_statut

  UPDATE CSR_STATUT

  SET STA_ID = @stadde,

  STA_DATE = CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS FLOAT)) AS DATETIME)

  WHERE DOS_ID = (SELECT DOS_ID

  FROM CSR_DOSSIER

  WHERE DOS_NODE = @numdde)

  END

  ELSE

  IF ( @Traitement = 'ObjetRempli' )

  BEGIN

  UPDATE CSR_REJET

  SET REJ_OBS = @Observation,

  REJ_OBJREJ = @ObjeRempli,

  REJ_STATUT = @rejetStatut,

  REJ_DATETRT = CAST (FLOOR (CAST (CURRENT_TIMESTAMP AS FLOAT)) AS DATETIME)

  WHERE DOS_NODE = @numdde

  ----MAJ table csr_statut

  UPDATE CSR_STATUT

  SET STA_ID = @stadde,

  STA_DATE = CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS FLOAT)) AS DATETIME)

  WHERE DOS_ID = (SELECT DOS_ID

  FROM CSR_DOSSIER

  WHERE DOS_NODE = @numdde)

  END

  ELSE

  IF ( @Traitement = 'Imprimer' )

  BEGIN

  UPDATE CSR_REJET

  SET REJ_DATRANS = CAST (FLOOR (CAST (CURRENT_TIMESTAMP AS FLOAT)) AS DATETIME),

  REJ_RefCourrier = @RefCourrier

  WHERE DOS_NODE = @numdde

  AND REJ_ID = @RejID

  DECLARE @dt DATETIME = CAST (FLOOR (CAST (CURRENT_TIMESTAMP AS FLOAT)) AS DATETIME)

  EXEC dbo.CREERFILE

  '00000000-0000-0000-0000-000000000000',

  @RefCourrier,

  @dt

  END

  END

  END

  ~ Lokesh Vij
  

  ---

  Guidelines for quicker answers on T-SQL question[/url]
  Guidelines for answers on Performance questions

  Link to my Blog Post --> www.SQLPathy.com[/url]

  Follow me @Twitter

  

• Lokesh Vij

  SSChampion

  Points: 10836

  More actions

  August 30, 2012 at 5:11 am

  #1531798

  Eugene Elutin (8/30/2012)

  ---

  Replace the line executing the proc with following:

  declare @dt datetime = CAST (FLOOR (CAST (CURRENT_TIMESTAMP as float )) as datetime)

  exec dbo.CreerFile '00000000-0000-0000-0000-000000000000',@RefCourrier , @dt

  Great!

  What a co-incidence, I also declared the variable as @dt 🙂

  Did not noticed that you posted the solution while I was investigating.

  Cheers!

  ~ Lokesh Vij
  

  ---

  Guidelines for quicker answers on T-SQL question[/url]
  Guidelines for answers on Performance questions

  Link to my Blog Post --> www.SQLPathy.com[/url]

  Follow me @Twitter