Innaznitutto ti ringrazio di vero cuore per avermi risposto.
Qui di seguito ti posto tutta la storer procedure:

codice:
USE [BSPF_GLOB]
GO
/****** Object:  StoredProcedure [dbo].[spLeaveRequestList2]    Script Date: 02/26/2009 14:59:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spLeaveRequestList2]
 @IdUser    int,
 @IdResponsible  int,
 @IdLeaveType  int,
 @LeaFromDate  varchar(14),
 @LeaToDate   varchar(14),
 @IdStatus   int,
 @IdApprovalManager  int,
 @query varchar(1000)
AS
BEGIN

 SET NOCOUNT ON;

 DECLARE @DateStart datetime
 DECLARE @DateEnd   datetime 

 IF @IdUser = -1
  SET @IdUser = 0
 
 IF @IdResponsible = -1
  SET @IdResponsible = 0

 IF @IdLeaveType = -1
  SET @IdLeaveType = 0

 IF @LeaFromDate = ''
   SET @LeaFromDate ='19000101000000'

 IF @LeaToDate = ''
  SET @LeaFromDate ='21001231000000'
 

IF @IdStatus = -1
  SET @IdStatus = 0

 IF @IdApprovalManager = -1
  SET @IdApprovalManager = 0
   
   SET @DateStart = dbo.StringToData(@LeaFromDate)
	SET @DateEnd   = dbo.StringToData(@LeaToDate)


IF @QUERY IS NULL

  SELECT  Ler.IDLeaveRequest, 
   Ler.IDUser, 
   Ler.IDLeaveType, 
   dbo.DataToString(Ler.leaFromDate) as leaFromDate, 
   dbo.DataToString(Ler.leaTODate) as leaToDate, 
            Ler.leaAmount, 
   Ler.leaAmountBaseUnit, 
   Ler.leaReason, 
   Ler.leaContactAddress, 
            Ler.leaContactPhone, 
   Ler.leaApproval, 
   isnull(Ler.IDApprovalManager, -1) as IDApprovalManager,
   Ler.IDStatus, 
            Ler.leaManagerNote
  FROM   leaveRequest Ler 
     
    WHERE   Ler.IDUser     =  ISNULL(@IdUser, Ler.IDUser)
   AND   Ler.IDLeaveType       =  ISNULL(@IdLeaveType, Ler.IDLeaveType)
   AND   Ler.leaFromDate    >= @DateStart   
   AND Ler.leaTODate         <= @DateEnd
   AND Ler.IDStatus    =  ISNULL(@IdStatus, Ler.IDStatus)
   AND ((Ler.IDApprovalManager =  ISNULL(@IdApprovalManager, Ler.IDApprovalManager))
   or (@IdApprovalManager is null and Ler.IDApprovalManager is null))
  



 ELSE 


  exec (' SELECT  Ler.IDLeaveRequest, 
   Ler.IDUser, 
   Ler.IDLeaveType, 
   dbo.DataToString(Ler.leaFromDate) as leaFromDate, 
   dbo.DataToString(Ler.leaTODate) as leaToDate, 
   Ler.leaAmount, 
   Ler.leaAmountBaseUnit, 
   Ler.leaReason, 
   Ler.leaContactAddress, 
   Ler.leaContactPhone, 
   Ler.leaApproval, 
   isnull(Ler.IDApprovalManager, -1) as IDApprovalManager,
   Ler.IDStatus, 
            Ler.leaManagerNote
  FROM   leaveRequest Ler 
     
    WHERE   Ler.IDUser     =  ISNULL(' + @IdUser +', Ler.IDUser)
    AND   Ler.IDLeaveType       =  ISNULL(' + @IdLeaveType + ', Ler.IDLeaveType)
    AND   Ler.leaFromDate    >= ' + @DateStart +'   
    AND Ler.leaTODate         <= '+ @DateEnd + '
    AND Ler.IDStatus    =  ISNULL(' + @IdStatus +', Ler.IDStatus)
    AND ((Ler.IDApprovalManager =  ISNULL(' + @IdApprovalManager + ', Ler.IDApprovalManager))
    or (' + @IdApprovalManager + ' is null and Ler.IDApprovalManager is null))
    AND   Ler.IDUser IN (' + @query + ')


')

END
Non lo postata prima perchè pensavo fosse troppo lunga da inserire in una discussione.

Cmq tornando a noi sopra c'e una function (dbo.StringToData(@LeaFromDate)) che appunto fa una trasformazione da stringa a data prendendo le sottostringhe.
il problema è nelle date all'interno dell'exec!
mi dà errore Incorrect Syntax near '1' che da quanto ho potuto vedere è il numero della prima data.

Grazie ancora