execute a procedure in another one

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » execute a procedure in another one

execute a procedure in another one

Rate Topic

Display Mode

Topic Options

Author

Message

marclas

Posted Thursday, August 30, 2012 4:53 AM

Grasshopper

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

Eugene Elutin

Posted Thursday, August 30, 2012 4:58 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 11:00 AM
Points: 2,543, Visits: 4,384

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!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1352108

Lokesh Vij

Posted Thursday, August 30, 2012 5:09 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 1,176, Visits: 1,253

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

Lokesh Vij

Posted Thursday, August 30, 2012 5:11 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 1,176, Visits: 1,253

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 Smile

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 »

Permissions