Fast-M
27-02-2009, 00.42.07
Salve, ho dei problemi con una stored procedure in t-sql di sql-server.
Ho bisogno di eseguire una query che mi selezioni un campo 'IDUser' di una tabella in base alla presenza di questo in un gruppo di valori interi.
Questa è la stored procerure:
USE [BSPF_GLOB]
GO
/****** Object: StoredProcedure [dbo].[spLeaveRequestListSub1] Script Date: 02/27/2009 00:28:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROC [dbo].[spLeaveRequestListSub1]
-- Add the parameters for the stored procedure here
@IdUser int,
@IdResponsible int,
@IdLeaveType int,
@LeaFromDate varchar(14),
@LeaToDate varchar(14),
@IdStatus int,
@IdApprovalManager int,
@idGroupUsers 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 = '19000202000000'
IF @LeaToDate = ''
SET @LeaToDate = '21001231000000'
IF @IdStatus = -1
SET @IdStatus = 0
IF @IdApprovalManager = -1
SET @IdApprovalManager = 0
SET @DateStart = dbo.StringToData(@LeaFromDate)
SET @DateEnd = dbo.StringToData(@LeaToDate)
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 (@idGroupUsers)
END
Se eseguo questa stored procedure con un exec nel seguente modo mi da errore di conversione:
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[spLeaveRequestListSub1]
@IdUser = 19,
@IdResponsible = 19,
@IdLeaveType = 4,
@LeaFromDate = NULL,
@LeaToDate = NULL,
@IdStatus = 50,
@IdApprovalManager = 18,
@idGroupUsers = N'72,73,69'
SELECT 'Return Value' = @return_value
GO
Questo è l'errore:
Conversion failed when converting the varchar value '72,73,69' to data type int.
Ho bisogno di eseguire una query che mi selezioni un campo 'IDUser' di una tabella in base alla presenza di questo in un gruppo di valori interi.
Questa è la stored procerure:
USE [BSPF_GLOB]
GO
/****** Object: StoredProcedure [dbo].[spLeaveRequestListSub1] Script Date: 02/27/2009 00:28:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROC [dbo].[spLeaveRequestListSub1]
-- Add the parameters for the stored procedure here
@IdUser int,
@IdResponsible int,
@IdLeaveType int,
@LeaFromDate varchar(14),
@LeaToDate varchar(14),
@IdStatus int,
@IdApprovalManager int,
@idGroupUsers 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 = '19000202000000'
IF @LeaToDate = ''
SET @LeaToDate = '21001231000000'
IF @IdStatus = -1
SET @IdStatus = 0
IF @IdApprovalManager = -1
SET @IdApprovalManager = 0
SET @DateStart = dbo.StringToData(@LeaFromDate)
SET @DateEnd = dbo.StringToData(@LeaToDate)
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 (@idGroupUsers)
END
Se eseguo questa stored procedure con un exec nel seguente modo mi da errore di conversione:
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[spLeaveRequestListSub1]
@IdUser = 19,
@IdResponsible = 19,
@IdLeaveType = 4,
@LeaFromDate = NULL,
@LeaToDate = NULL,
@IdStatus = 50,
@IdApprovalManager = 18,
@idGroupUsers = N'72,73,69'
SELECT 'Return Value' = @return_value
GO
Questo è l'errore:
Conversion failed when converting the varchar value '72,73,69' to data type int.