Hi Guys,
I've just upgraded from the version 2 beta 2 to version 2.0.0.387.
I have several custom stored procs in my app that contain construct sql dynamically and retrun the result by calling EXEC sp_executesql...
In the beta 2 version for my procedure "usp_Address_Lookup" .netTiers generated the corresponding method "IDataReader Lookup(...)" which is what I want.
In version 2.0.0.387 for the same procedure the return type is void instead of IDataReader i.e. "void Lookup(...)".
This seems like a bug to me. Below is one of the stored procs that I'm having this problem with:
-------
ALTER procedure [dbo].[usp_Address_Lookup]
(
@Info varchar(100) = NULL,
@Unit varchar(50) = NULL,
@Lot varchar(50) = NULL,
@Number varchar(50) = NULL,
@Street varchar(100) = NULL,
@Suburb varchar(100) = NULL,
@State varchar(10) = NULL,
@Postcode varchar(10) = NULL,
@MaxRows int = 10,
@Debug bit = 0
)
as
declare @sql nvarchar(max), @paramlist nvarchar(max)
select
@sql = 'SELECT TOP ' + CONVERT(varchar(10), @MaxRows) + ' '
select
@sql = @sql + 'AddressId, Info, Unit, Lot, Number, Street, Suburb, StateProvince, Postcode from Address'
select
@sql = @sql + ' WHERE 1 = 1'
if
(@Info IS NOT NULL) AND (@Info <> '')
select @sql = @sql + ' AND Address.Info LIKE @xInfo + ''%'''
if
(@Unit IS NOT NULL) AND (@Unit <> '')
select @sql = @sql + ' AND Address.Unit LIKE ''%'' + @xUnit + ''%'''
if
(@Lot IS NOT NULL) AND (@Lot <> '')
select @sql = @sql + ' AND Address.Lot LIKE ''%'' + @xLot + ''%'''
if
(@Number IS NOT NULL) AND (@Number <> '')
select @sql = @sql + ' AND Address.Number LIKE ''%'' + @xNumber + ''%'''
if
(@Street IS NOT NULL) AND (@Street <> '')
select @sql = @sql + ' AND Address.Street LIKE @xStreet + ''%'''
if
(@Suburb IS NOT NULL) AND (@Suburb <> '')
select @sql = @sql + ' AND Address.Suburb LIKE @xSuburb + ''%'''
if
(@State IS NOT NULL) AND (@State <> '')
select @sql = @sql + ' AND Address.StateProvince LIKE @xState + ''%'''
if
(@Postcode IS NOT NULL) AND (@Postcode <> '')
select @sql = @sql + ' AND Address.Postcode LIKE @xPostcode + ''%'''
select @paramlist = '@xInfo varchar(100), @xUnit varchar(50), @xLot varchar(50), @xNumber varchar(50),
@xStreet varchar(100), @xSuburb varchar(100), @xState varchar(100), @xPostcode varchar(100)'
if
@Debug = 1 print @sql
EXEC
sp_executesql @sql, @paramlist, @Info, @Unit, @Lot, @Number, @Street, @Suburb, @State, @Postcode
-------
Any ideas? It seems to me that netTiers is assuming that this proc does not return any rows. Is there something else I need to specify in the proc to let netTiers know what to expect as a result?
Cheers,
Dean