Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


execute a procedure in another one


execute a procedure in another one

Author
Message
marclas
marclas
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 36
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
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3040 Visits: 5478
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
Lokesh Vij
Lokesh Vij
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1564 Visits: 1599
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter


Lokesh Vij
Lokesh Vij
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1564 Visits: 1599
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search