azadeh_1510
یک شنبه 20 بهمن 1392, 11:10 صبح
دوستان سلام
نمیدونم خطای این SP چی هست؟
USE [Azar]
GO
/****** Object: StoredProcedure [dbo].[getPagedMedicalsAdvanceSearch] Script Date: 02/09/2014 10:59:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getPagedMedicalsAdvanceSearch]
@Title nvarchar(250),@IssuedDateFrom nvarchar(250),@IssuedDateTo nvarchar(250),@IssuedCostFrom decimal,@IssuedCostTo decimal,
@PaymentDateFrom nvarchar(250),@PaymentDateTo nvarchar(250),@PaymentFrom decimal,@PaymentTo decimal,@DocumentType int,
@Documnet_DateFrom nvarchar(250),@Documnet_DateTo nvarchar(250),@State XML,
@pageIndex int,
@pageSize int,
@order nvarchar(255),
@isAsc bit=1
AS
BEGIN
SET NOCOUNT ON;
DECLARE @PageWhere nvarchar(1000)
DECLARE @Where nvarchar(max)
DECLARE @OrderType nvarchar(4)
DECLARE @handle INT
IF (@isAsc = 0) SET @OrderType = 'Desc'
ELSE SET @OrderType = 'ASC'
SET @PageWhere = 'RowNum BETWEEN (' + CAST(((@pageIndex - 1) * @pageSize + 1) AS varchar(50)) + ') AND (' + CAST((@pageIndex * @pageSize) AS Varchar(50)) + ')'
EXEC sp_xml_preparedocument @handle OUTPUT, @State
SET @Where = '1=1 '
IF (ISNULL(@Title,'')<>'')
SET @Where = @Where + 'AND ( dbo.MedicalCosts.Title LIKE N'''+'%' + @Title + N'%'+''')'
IF (ISNULL(@IssuedDateFrom,'')<>'')
SET @Where = @Where + 'AND ( dbo.MedicalCosts.IssuedDate >= ''' + @IssuedDateFrom +''')'
IF (ISNULL(@IssuedDateTo,'')<>'')
SET @Where = @Where + 'AND ( dbo.MedicalCosts.IssuedDate <= ''' + @IssuedDateTo + ''')'
IF (ISNULL(@IssuedCostFrom,'')<>'')
SET @Where = @Where + 'AND (dbo.MedicalCosts.IssuedCost <= ''' + @IssuedCostFrom +''')'
IF (ISNULL(@IssuedCostTo,'')<>'')
SET @Where = @Where + 'AND (dbo.MedicalCosts.IssuedCost <= ''' + @IssuedCostTo +''')'
IF (ISNULL(@PaymentDateFrom,'')<>'')
SET @Where = @Where + 'AND (dbo.MedicalCosts.PaymentDate >= ''' + @PaymentDateFrom +''')'
IF (ISNULL(@PaymentDateTo,'')<>'')
SET @Where = @Where + 'AND (dbo.MedicalCosts.PaymentDate <= ''' + @PaymentDateTo + ''')'
IF (ISNULL(@PaymentFrom,'')<>'')
SET @Where = @Where + 'AND ( dbo.MedicalCosts.Payment <= ''' + @PaymentFrom +''')'
IF (ISNULL(@PaymentTo,'')<>'')
SET @Where = @Where + 'AND ( dbo.MedicalCosts.Payment <= ''' + @PaymentTo +''')'
IF (ISNULL(@DocumentType,'')<>'')
SET @Where = @Where + 'AND ( dbo.MedicalDocuments.MedicalDocumentType =''' + @DocumentType + ''')'
IF (ISNULL(@Documnet_DateFrom,'')<>'')
SET @Where = @Where + 'AND (dbo.MedicalDocuments.IssueDate >= ''' + @Documnet_DateFrom +''')'
IF (ISNULL(@Documnet_DateTo,'')<>'')
SET @Where = @Where + 'AND (dbo.MedicalDocuments.IssueDate <= ''' + @Documnet_DateTo + ''')'
IF (ISNULL(@State,'')<>'')
SET @Where = @Where +' AND (dbo.MedicalCostStates.Id IN (SELECT * FROM OPENXML ('+@handle + '''/ids/id'') WITH (id INT ''.'') )'
EXEC sp_xml_removedocument @handle
EXECUTE('
SELECT *
FROM (
SELECT * , ROW_NUMBER() OVER (ORDER BY '+@Order+ ' ' + @OrderType + ' ) AS RowNum
FROM (
SELECT dbo.MedicalCosts.Id,
dbo.MedicalCosts.InsuredPersonEmployeeCode,
dbo.Employees.FullName AS InsuredPerson,
dbo.MedicalCosts.DedicatedTo,
dbo.MedicalCosts.State AS StateId,
dbo.MedicalCostStates.Name AS State,
dbo.MedicalCosts.RelativeType AS RelativeTypeId,
dbo.MedicalCostRelativeTypes.Name AS RelativeType,
dbo.MedicalCosts.Title,
dbo.MedicalCosts.IssuedCost,
dbo.MedicalCosts.IssuedDate,
dbo.MedicalCosts.Payment,
dbo.MedicalCosts.PaymentDate,
dbo.MedicalCosts.Description,
dbo.MedicalDocuments.MedicalDocumentType,
dbo.MedicalDocuments.IssueDate
FROM dbo.MedicalCosts INNER JOIN
dbo.Employees ON dbo.MedicalCosts.InsuredPersonEmployeeCode = dbo.Employees.Code INNER JOIN
dbo.MedicalCostStates ON dbo.MedicalCosts.State = dbo.MedicalCostStates.Id INNER JOIN
dbo.MedicalCostRelativeTypes ON dbo.MedicalCosts.RelativeType = dbo.MedicalCostRelativeTypes.Id INNER JOIN
dbo.MedicalDocuments ON dbo.MedicalCosts.Id = dbo.MedicalDocuments.MedicalCost_Id
WHERE ' + @Where+ '
) AS V_MedicalCost1
) AS V_MedicalCost2
WHERE ' + @PageWhere + ' ORDER BY ' + @Order + ' ' + @OrderType);
END
پیام خطاش این هست:
Msg 402, Level 16, State 1, Procedure getPagedMedicalsAdvanceSearch, Line 49
The data types xml and varchar are incompatible in the not equal to operator.
Msg 402, Level 16, State 1, Procedure getPagedMedicalsAdvanceSearch, Line 50
The data types nvarchar(max) and xml are incompatible in the add operator.
Msg 402, Level 16, State 1, Procedure getPagedMedicalsAdvanceSearch, Line 51
The data types xml and varchar are incompatible in the not equal to operator.
نمیدونم خطای این SP چی هست؟
USE [Azar]
GO
/****** Object: StoredProcedure [dbo].[getPagedMedicalsAdvanceSearch] Script Date: 02/09/2014 10:59:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getPagedMedicalsAdvanceSearch]
@Title nvarchar(250),@IssuedDateFrom nvarchar(250),@IssuedDateTo nvarchar(250),@IssuedCostFrom decimal,@IssuedCostTo decimal,
@PaymentDateFrom nvarchar(250),@PaymentDateTo nvarchar(250),@PaymentFrom decimal,@PaymentTo decimal,@DocumentType int,
@Documnet_DateFrom nvarchar(250),@Documnet_DateTo nvarchar(250),@State XML,
@pageIndex int,
@pageSize int,
@order nvarchar(255),
@isAsc bit=1
AS
BEGIN
SET NOCOUNT ON;
DECLARE @PageWhere nvarchar(1000)
DECLARE @Where nvarchar(max)
DECLARE @OrderType nvarchar(4)
DECLARE @handle INT
IF (@isAsc = 0) SET @OrderType = 'Desc'
ELSE SET @OrderType = 'ASC'
SET @PageWhere = 'RowNum BETWEEN (' + CAST(((@pageIndex - 1) * @pageSize + 1) AS varchar(50)) + ') AND (' + CAST((@pageIndex * @pageSize) AS Varchar(50)) + ')'
EXEC sp_xml_preparedocument @handle OUTPUT, @State
SET @Where = '1=1 '
IF (ISNULL(@Title,'')<>'')
SET @Where = @Where + 'AND ( dbo.MedicalCosts.Title LIKE N'''+'%' + @Title + N'%'+''')'
IF (ISNULL(@IssuedDateFrom,'')<>'')
SET @Where = @Where + 'AND ( dbo.MedicalCosts.IssuedDate >= ''' + @IssuedDateFrom +''')'
IF (ISNULL(@IssuedDateTo,'')<>'')
SET @Where = @Where + 'AND ( dbo.MedicalCosts.IssuedDate <= ''' + @IssuedDateTo + ''')'
IF (ISNULL(@IssuedCostFrom,'')<>'')
SET @Where = @Where + 'AND (dbo.MedicalCosts.IssuedCost <= ''' + @IssuedCostFrom +''')'
IF (ISNULL(@IssuedCostTo,'')<>'')
SET @Where = @Where + 'AND (dbo.MedicalCosts.IssuedCost <= ''' + @IssuedCostTo +''')'
IF (ISNULL(@PaymentDateFrom,'')<>'')
SET @Where = @Where + 'AND (dbo.MedicalCosts.PaymentDate >= ''' + @PaymentDateFrom +''')'
IF (ISNULL(@PaymentDateTo,'')<>'')
SET @Where = @Where + 'AND (dbo.MedicalCosts.PaymentDate <= ''' + @PaymentDateTo + ''')'
IF (ISNULL(@PaymentFrom,'')<>'')
SET @Where = @Where + 'AND ( dbo.MedicalCosts.Payment <= ''' + @PaymentFrom +''')'
IF (ISNULL(@PaymentTo,'')<>'')
SET @Where = @Where + 'AND ( dbo.MedicalCosts.Payment <= ''' + @PaymentTo +''')'
IF (ISNULL(@DocumentType,'')<>'')
SET @Where = @Where + 'AND ( dbo.MedicalDocuments.MedicalDocumentType =''' + @DocumentType + ''')'
IF (ISNULL(@Documnet_DateFrom,'')<>'')
SET @Where = @Where + 'AND (dbo.MedicalDocuments.IssueDate >= ''' + @Documnet_DateFrom +''')'
IF (ISNULL(@Documnet_DateTo,'')<>'')
SET @Where = @Where + 'AND (dbo.MedicalDocuments.IssueDate <= ''' + @Documnet_DateTo + ''')'
IF (ISNULL(@State,'')<>'')
SET @Where = @Where +' AND (dbo.MedicalCostStates.Id IN (SELECT * FROM OPENXML ('+@handle + '''/ids/id'') WITH (id INT ''.'') )'
EXEC sp_xml_removedocument @handle
EXECUTE('
SELECT *
FROM (
SELECT * , ROW_NUMBER() OVER (ORDER BY '+@Order+ ' ' + @OrderType + ' ) AS RowNum
FROM (
SELECT dbo.MedicalCosts.Id,
dbo.MedicalCosts.InsuredPersonEmployeeCode,
dbo.Employees.FullName AS InsuredPerson,
dbo.MedicalCosts.DedicatedTo,
dbo.MedicalCosts.State AS StateId,
dbo.MedicalCostStates.Name AS State,
dbo.MedicalCosts.RelativeType AS RelativeTypeId,
dbo.MedicalCostRelativeTypes.Name AS RelativeType,
dbo.MedicalCosts.Title,
dbo.MedicalCosts.IssuedCost,
dbo.MedicalCosts.IssuedDate,
dbo.MedicalCosts.Payment,
dbo.MedicalCosts.PaymentDate,
dbo.MedicalCosts.Description,
dbo.MedicalDocuments.MedicalDocumentType,
dbo.MedicalDocuments.IssueDate
FROM dbo.MedicalCosts INNER JOIN
dbo.Employees ON dbo.MedicalCosts.InsuredPersonEmployeeCode = dbo.Employees.Code INNER JOIN
dbo.MedicalCostStates ON dbo.MedicalCosts.State = dbo.MedicalCostStates.Id INNER JOIN
dbo.MedicalCostRelativeTypes ON dbo.MedicalCosts.RelativeType = dbo.MedicalCostRelativeTypes.Id INNER JOIN
dbo.MedicalDocuments ON dbo.MedicalCosts.Id = dbo.MedicalDocuments.MedicalCost_Id
WHERE ' + @Where+ '
) AS V_MedicalCost1
) AS V_MedicalCost2
WHERE ' + @PageWhere + ' ORDER BY ' + @Order + ' ' + @OrderType);
END
پیام خطاش این هست:
Msg 402, Level 16, State 1, Procedure getPagedMedicalsAdvanceSearch, Line 49
The data types xml and varchar are incompatible in the not equal to operator.
Msg 402, Level 16, State 1, Procedure getPagedMedicalsAdvanceSearch, Line 50
The data types nvarchar(max) and xml are incompatible in the add operator.
Msg 402, Level 16, State 1, Procedure getPagedMedicalsAdvanceSearch, Line 51
The data types xml and varchar are incompatible in the not equal to operator.