SQL Clone
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
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 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
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5022 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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2070 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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2070 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