execute a procedure in another one

  • 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

  • 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]

  • 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

  • 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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply