PDA

Visualizza versione completa : Stored Procedure SqlServer


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.

miciomao
27-02-2009, 09.57.39
@idGroupUsers
che tipo di campo interroga ?
qual' il suo formato?


Ciao
Alex