SQL Report error : There is an error in the query. Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query.

Question

Hi

I am creating a new Sql report and using a stored procedure that accepts several parameters and returns a result set.
When I try to create the report and call the stored procedure I get the following error:

There is an error in the query. Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query.

My stored procedure is quite long and I changed all the cast to convert but it still throws the error. Below is my stored procedure.

ALTER Procedure [dbo].[Search_SalesHistory_Report]

@VolumeNo  varchar(5),
@RateNumber varchar(12),
@DeedsTown Varchar(12),
@StreetNo  varchar(50),
@StreetName varchar(50),
@UseCode varchar(200),  /*Coma Seprated String IN*/
@RateCode varchar(200), /*Coma Seprated String IN*/
@ZoneCode varchar(200), /*no use--Coma Seprated String IN*/
@OwnerName varchar(150),
@SalePrice  varchar(10),
@SalePriceTo varchar(10),
@Extent varchar(150),
@ExtentTo varchar(150),
@SaleDate varchar(20),
@SaleDateTo Varchar(20),
@SearchType varchar(100),
@SchemeName varchar(100)

As
SET NOCOUNT ON;

Declare @WhereSql nvarchar(max)
Declare @isIn int
Declare @JOIN nvarchar(max)
Declare @SQL nvarchar(max),@nStatement nvarchar(max)
Declare @sql1 nvarchar(max)
Declare @PDataSql1 nvarchar(max)
set @isIn = 0;
declare @count int
set @count =0

set @SQL = N'Select distinct top 10 '

set @SQL = @SQL + N'dbo.P_FlatRegister.PIN,  ISNULL
              ((SELECT     Caption
                  FROM         dbo.C_ListItems AS C_ListItems_2 WITH (nolock)
                  WHERE     (ItemID = convert(int,dbo.P_FlatRegister.VolumeNumber))), ''0'') + ''-'' + ISNULL(dbo.P_FlatRegister.RateNumber, '''')
          + ''-'' + ISNULL(dbo.P_FlatRegister.Subcode, ''0'') AS [Rate Number], ISNULL(dbo.P_FlatRegister.StreetNumber, '''')
          + '' '' + ISNULL(dbo.P_FlatRegister.StreetName, '''') + '', '' + ISNULL
              ((SELECT     Caption
                  FROM         dbo.C_ListItems AS C_ListItems_1 WITH (nolock)
                  WHERE     (ItemID = dbo.P_FlatRegister.Suburb)), '''') AS Address, dbo.P_Register.Description AS [Property Description], dbo.P_Versions.Usecode,
          dbo.C_RatingCategories.Caption AS RatingCategory, dbo.C_Statuses.Description AS Status,
           CONVERT(decimal(18, 0), dbo.P_Versions.Extent) AS Extent,
          dbo.P_Versions.AdjustedValue AS MarketValue,
  isnull((select top 1 name from p_owners as k with (nolock) where k.saleid=p_sales.saleid order by k.ownerid),'''') as Owner,
  isnull((select top 1 name from p_owners as k with (nolock) where k.PIN=p_sales.PIN and k.saleid<p_sales.saleid order by k.saleid desc,k.ownerid asc),'''') as Seller,
  convert(decimal(18,0),dbo.P_Sales.SalePrice) as SalesPrice,
  convert(varchar(11),P_Sales.SaleDate) As SaleDate,dbo.P_Sales.TitleDeed,
     (select top 1 coalesce(C_ListItems.caption,'''')
  FROM     C_ListItems inner join p_data on p_data.data = C_ListItems.itemid
  where p_data.status = ''C'' and p_data.attributeid = 100 and p_data.pin = dbo.P_Versions.pin) AS [View],
     (select top 1 coalesce(C_ListItems.caption,'''')
  FROM     C_ListItems inner join p_data on p_data.data = C_ListItems.itemid
  where p_data.status = ''C'' and p_data.attributeid = 101 and p_data.pin = dbo.P_Versions.pin) AS [Security],
     (select top 1 coalesce(C_ListItems.caption,'''')
  FROM     C_ListItems inner join p_data on p_data.data = C_ListItems.itemid
  where p_data.status = ''C'' and p_data.attributeid = 102 and p_data.pin = dbo.P_Versions.pin) AS [External Noise],
     (select top 1 coalesce(C_ListItems.caption,'''')
  FROM     C_ListItems inner join p_data on p_data.data = C_ListItems.itemid
  where p_data.status = ''C'' and p_data.attributeid = 103 and p_data.pin = dbo.P_Versions.pin) AS [Topography],
     (select top 1 coalesce(C_ListItems.caption,'''')
  FROM     C_ListItems inner join p_data on p_data.data = C_ListItems.itemid
  where p_data.status = ''C'' and p_data.attributeid = 104 and p_data.pin = dbo.P_Versions.pin) AS [Quality],
     (select top 1 coalesce(C_ListItems.caption,'''')
  FROM     C_ListItems inner join p_data on p_data.data = C_ListItems.itemid
  where p_data.status = ''C'' and p_data.attributeid = 105 and p_data.pin = dbo.P_Versions.pin) AS [Condition],
     (select top 1 coalesce(C_ListItems.caption,'''')
  FROM     C_ListItems inner join p_data on p_data.data = C_ListItems.itemid
  where p_data.status = ''C'' and p_data.attributeid = 106 and p_data.pin = dbo.P_Versions.pin) AS [ExteriorWalls],
     (select top 1 coalesce(C_ListItems.caption,'''')
  FROM     C_ListItems inner join p_data on p_data.data = C_ListItems.itemid
  where p_data.status = ''C'' and p_data.attributeid = 107 and p_data.pin = dbo.P_Versions.pin) AS [# Bedrooms],
(select top 1 coalesce(C_ListItems.caption,'''')
  FROM     C_ListItems inner join p_data on p_data.data = C_ListItems.itemid
  where p_data.status = ''C'' and p_data.attributeid = 110 and p_data.pin = dbo.P_Versions.pin) AS [# Bathrooms],
(select top 1 coalesce(C_ListItems.caption,'''')
  FROM     C_ListItems inner join p_data on p_data.data = C_ListItems.itemid
  where p_data.status = ''C'' and p_data.attributeid = 111 and p_data.pin = dbo.P_Versions.pin) AS [# Storeys],
(select top 1 coalesce(C_ListItems.caption,'''')
  FROM     C_ListItems inner join p_data on p_data.data = C_ListItems.itemid
  where p_data.status = ''C'' and p_data.attributeid = 112 and p_data.pin = dbo.P_Versions.pin) AS [# Houses],
(select top 1 coalesce(p_data.Data,'''')
  FROM p_data where p_data.status = ''C'' and p_data.attributeid = 113 and p_data.pin = dbo.P_Versions.pin) AS [TLA 1],
  (select top 1 coalesce(p_data.Data,'''')
  FROM p_data where p_data.status = ''C'' and p_data.attributeid = 114 and p_data.pin = dbo.P_Versions.pin) AS [TLA 2],
  (select top 1 coalesce(p_data.Data,'''')
  FROM p_data where p_data.status = ''C'' and p_data.attributeid = 115 and p_data.pin = dbo.P_Versions.pin) AS [TLA 3],
  (select top 1 coalesce(p_data.Data,'''')
  FROM p_data where p_data.status = ''C'' and p_data.attributeid = 116 and p_data.pin = dbo.P_Versions.pin) AS [Year Built],
  (select top 1 coalesce(C_ListItems.caption,'''')
  FROM     C_ListItems inner join p_data on p_data.data = C_ListItems.itemid
  where p_data.status = ''C'' and p_data.attributeid = 117 and p_data.pin = dbo.P_Versions.pin) AS [Unfinished building],
  (select top 1 coalesce(p_data.Data,'''')
  FROM p_data where p_data.status = ''C'' and p_data.attributeid = 119 and p_data.pin = dbo.P_Versions.pin) AS [Garage],
  (select top 1 coalesce(p_data.Data,'''')
  FROM p_data where p_data.status = ''C'' and p_data.attributeid = 120 and p_data.pin = dbo.P_Versions.pin) AS [Carport],
  (select top 1 coalesce(p_data.Data,'''')
  FROM p_data where p_data.status = ''C'' and p_data.attributeid = 121 and p_data.pin = dbo.P_Versions.pin) AS [Granny Flat],
  (select top 1 coalesce(p_data.Data,'''')
  FROM p_data where p_data.status = ''C'' and p_data.attributeid = 122 and p_data.pin = dbo.P_Versions.pin) AS [Servants Quarters],
  (select top 1 coalesce(C_ListItems.caption,'''')
  FROM     C_ListItems inner join p_data on p_data.data = C_ListItems.itemid
  where p_data.status = ''C'' and p_data.attributeid = 124 and p_data.pin = dbo.P_Versions.pin) AS [Pool],
  (select top 1 coalesce(p_data.Data,'''')
  FROM p_data where p_data.status = ''C'' and p_data.attributeid = 123 and p_data.pin = dbo.P_Versions.pin) AS [# Shacks],
  (select top 1 coalesce(C_ListItems.caption,'''')
  FROM     C_ListItems inner join p_data on p_data.data = C_ListItems.itemid
  where p_data.status = ''C'' and p_data.attributeid = 186 and p_data.pin = dbo.P_Versions.pin) AS [Access]';
    

set @isIn = 1;
set @WhereSql = ''

/* Create Where Condition*/

If( len(@VolumeNo)>0)
 Begin
set @WhereSql = N' and convert(Varchar(10),P_FlatRegister.VolumeNumber) LIKE ''%' + @VolumeNo +'%''';
End

If( len(@RateNumber)>0) Begin  if(@isIn != 1) BEGIN set @isIn = 1; set @WhereSql = N' WHERE P_FlatRegister.RateNumber=''' + @RateNumber +''''; end
   else Begin set @WhereSql = @WhereSql + N' and P_FlatRegister.RateNumber LIKE ''%' + @RateNumber + '%'''; END
End

If( len(@DeedsTown)>0) Begin if(@isIn != 1) BEGIN set @isIn = 1; set @WhereSql = N' WHERE  convert(Varchar(12), P_FlatRegister.DeedsTown)=''' + @DeedsTown +''''; end
   else Begin set @WhereSql = @WhereSql + N' and P_FlatRegister.DeedsTown LIKE ''%' + @DeedsTown +'%'''; END
End

If( len(@StreetNo)>0) Begin if(@isIn != 1) BEGIN set @isIn = 1; set @WhereSql = N'  WHERE  P_FlatRegister.StreetNumber=''' + @StreetNo +''''; end
   else Begin set @WhereSql = @WhereSql + N' and P_FlatRegister.StreetNumber like ''%' + @StreetNo +'%'''; END
End

If( len(@StreetName)>0) Begin if(@isIn != 1) BEGIN set @isIn = 1; set @WhereSql = N'  WHERE  P_FlatRegister.StreetName=''' + @StreetName+''''; end
   else Begin set @WhereSql = @WhereSql + N' and P_FlatRegister.StreetName LIKE ''%' + @StreetName +'%''';  END
End

If( len(@UseCode)>0) Begin if(@isIn != 1) BEGIN set @isIn = 1; set @WhereSql = N'  WHERE  P_VERSIONS.UseCode in (' + @UseCode + ')'; end
   else Begin set @WhereSql = @WhereSql + N' and P_VERSIONS.UseCode in (' + @UseCode + ')'; END
End

If( len(@RateCode)>0)Begin if(@isIn != 1) BEGIN set @isIn = 1; set @WhereSql = N'  WHERE  P_VERSIONS.Category in (' + @RateCode + ')'; end
   else Begin set @WhereSql = @WhereSql + N' and P_VERSIONS.Category  in (' + @RateCode + ')'; END
End

If( len(@OwnerName)>0) Begin if(@isIn != 1) BEGIN set @isIn = 1; set @WhereSql = N'  WHERE  P_Owners.Name =''' + @OwnerName +'''' ; end
   else Begin set @WhereSql = @WhereSql + N' and P_Owners.Name LIKE ''%' + @OwnerName  +'%''' ; END
End

If(  ( len(@SalePrice)>0) and (@SalePrice!='0') ) Begin if(@isIn != 1) BEGIN set @isIn = 1; set @WhereSql = N'  WHERE  P_Sales.SalePrice >=' + @SalePrice  ;end
   else Begin set @WhereSql = @WhereSql + N' and P_Sales.SalePrice >=' + @SalePrice   ; END
End

If(  ( len(@SalePriceTo)>0) and (@SalePriceTo!='0') ) Begin if(@isIn != 1) BEGIN set @isIn = 1; set @WhereSql = N'  WHERE  P_Sales.SalePrice <=' + @SalePriceTo  ; end
   else Begin set @WhereSql = @WhereSql + N' and P_Sales.SalePrice <=' + @SalePriceTo   ; END
End

If(  ( len(@Extent)>0) and (@Extent!='0') ) Begin if(@isIn != 1) BEGIN set @isIn = 1; set @WhereSql = N'  WHERE  P_VERSIONS.Extent >=' + @Extent  ; end
   else Begin set @WhereSql = @WhereSql + N' and P_VERSIONS.Extent >=' + @Extent   ; END
End

If(  ( len(@ExtentTo)>0) and (@ExtentTo!='0') ) Begin if(@isIn != 1) BEGIN set @isIn = 1; set @WhereSql = N'  WHERE  P_VERSIONS.Extent <=' + @ExtentTo  ;end
   else Begin set @WhereSql = @WhereSql + N' and P_VERSIONS.Extent <=' + @ExtentTo   ; END
End

If( len(@SaleDate)>0) Begin if(@isIn != 1) BEGIN set @isIn = 1; set @WhereSql = N'  WHERE  P_Sales.SaleDate >=''' + @SaleDate +'''' ; end
   else Begin set @WhereSql = @WhereSql + N' and P_Sales.SaleDate >=''' + @SaleDate  +'''' ; END
End

If( len(@SaleDateTo)>0) Begin if(@isIn != 1) BEGIN set @isIn = 1; set @WhereSql = N'  WHERE  P_Sales.SaleDate <=''' + @SaleDateTo +'''' ;end
   else Begin set @WhereSql = @WhereSql + N' and P_Sales.SaleDate <=''' + @SaleDateTo  +'''' ;END
End

If( len(@SchemeName)>0) Begin if(@isIn != 1) BEGIN set @isIn = 1; set @WhereSql = N'  WHERE  P_Flatst.SchemeName=''' + @SchemeName+''''; end
   else Begin set @WhereSql = @WhereSql + N' and P_Flatst.Schemename LIKE ''%' + @SchemeName +'%''';  END
End

If( len(@SearchType)>0)
Begin

 if(@SearchType='1') --FT
 begin  
   if(@isIn != 1) BEGIN set @isIn = 1; set @WhereSql = N'  WHERE  P_register.Typeid <> 4' ;end
   else Begin set @WhereSql = @WhereSql + N' and P_register.Typeid <> 4' ;END
 end

 else if(@SearchType='4')
 begin
   if(@isIn != 1) BEGIN set @isIn = 1; set @WhereSql = N'  WHERE  P_register.Typeid = 4' ;end
   else Begin set @WhereSql = @WhereSql + N' and P_register.Typeid = 4' ;END
 end

End

/*End of Where Condition*/

set @JOIN =N' FROM P_FlatRegister with (nolock) INNER JOIN P_REGISTER with (nolock) on P_FlatRegister.PIN = P_REGISTER.PIN
INNER JOIN P_VERSIONS with (nolock) on P_REGISTER.VersionID = P_VERSIONS.VersionID
INNER JOIN P_Sales with (nolock) on P_REGISTER.PIN = P_Sales.PIN INNER JOIN
dbo.C_Statuses WITH (nolock) ON dbo.P_sales.Status = dbo.C_Statuses.Status INNER JOIN
dbo.C_UseCodes with (nolock) ON dbo.P_Versions.Usecode = dbo.C_UseCodes.UseCode LEFT OUTER JOIN
dbo.C_RatingCategories  with (nolock) ON dbo.P_Versions.Category = dbo.C_RatingCategories.RatingCategoryCode
LEFT OUTER JOIN P_Owners with (nolock) on P_SALES.Saleid = P_Owners.saleid LEFT OUTER JOIN
P_FLATST with (nolock) on P_REGISTER.parentpin=P_FLATST.pin
WHERE P_Register.status<>''W''
and P_Sales.status<>''E''
and P_Sales.status<>''W'''

set @SQL = @SQL + N'' +  @JOIN + N'' + isnull(@WhereSql,N'');

set @sql1 = 'Select   count(*) ' + '' +  @JOIN + '' + isnull(@WhereSql,'');

set @nStatement = CONVERT(nvarchar(max),@SQL)

print len(@nStatement)

exec sp_executesql @sql1, N'@count INT OUTPUT',@count OUTPUT  /*--counter*/

exec sp_executesql @nStatement, N'@count INT OUTPUT',@count OUTPUT   /*--Record Output*/