execute a procedure in another one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

execute a procedure in another one

Author

Topic

marclas
Starting Member

16 Posts

Posted - 2012-08-30 : 06:53:12

hi i am trying unsuccessfully to execute the following procedure in another one
1-[code="SQL"] 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
[/code]

procedure 2
[code="sql"]
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
[/code]

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

Marclas

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-30 : 07:01:24

Change that part of the code to this:

	        DECLARE @param3 DATETIME; 
	        SET @param3 = DATEADD(dd,DATEDIFF(dd,0,CURRENT_TIMESTAMP),0);
	        EXEC dbo.CreerFile '00000000-0000-0000-0000-000000000000',
	             @RefCourrier ,@param3

I took the liberty of changing the way you strip off the time portion from the current_timestamp. The way you have done it should work, but it is depending on the internal implementation of the datetime data type for it to work correctly. There are a few different ways you can strip off the time part - see Madhivanan's blog here: http://beyondrelational.com/modules/2/blogs/70/posts/17535/different-ways-to-remove-time-part-from-datetime-values.aspx

shaggy
Posting Yak Master

248 Posts

Posted - 2012-08-30 : 07:08:43

What it is mean -error "Msg 102, Niveau 15, État 1, Procédure EnregistrerRejet, Ligne 86 ?

1)set language english -paste this at the top of the script
2)then run the script
3)post the error

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources