Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

execute a procedure in another one Expand / Collapse
Author
Message
Posted Thursday, August 30, 2012 4:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 11, 2012 9:38 AM
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
Post #1352100
Posted Thursday, August 30, 2012 4:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1352108
Posted Thursday, August 30, 2012 5:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 9:20 AM
Points: 1,372, Visits: 1,567
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

Post #1352111
Posted Thursday, August 30, 2012 5:11 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 9:20 AM
Points: 1,372, Visits: 1,567
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

Post #1352112
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse