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:
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*/