execute a procedure in another one

Question

Post reply

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

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

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

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 · Answer 2

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

~ 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 · Answer 3

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