Welcome to the CodeSmith Community!

Custom stored procedures with dynamic sql returning void

.netTiers

A description has not yet been added to this group.

Custom stored procedures with dynamic sql returning void

Answered (Verified) This question is answered

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

  • Post Points: 35
Verified Answer
  • Hello,

    Please take a look at the following thread for more information: http://community.codesmithtools.com/support_forums1/f/9/t/11437.aspx. The key is to check the result set being returned by the following queries. The NULL Statements need to be added for each parameter of your stored procedure.

    SET FMTONLY ON
    
    EXEC [dbo].[stored_procedure]
    
        NULL
    
    SET FMTONLY OFF
    
    -- Table Valued Function..
    
    SET FMTONLY ON
    
    SELECT * FROM [dbo].[sql_function] (NULL)
    
    SET FMTONLY OFF

    I would check to see if each parameter is null and if so return a set of columns like select * from dbo.tablename... Sometimes, SQL Server has a hard time parsing the results. If this is the case, this is the only work around to solving this issue. As with the post above, FMTONLY is somewhat limited and will evaluate all queries that are inside of if logic..

    If configuring your stored procedure and debugging it doesn't pan out (very rarely is this the case Sad). The only way to modify what is generated is to manually update the templates to account for this stored procedure.

    @GRIER, you would need to specify the TotalRowCount as an output parameter in the actual stored procedure parameter definition.

     

    Thanks

    -Blake Niemyjski

     

    Blake Niemyjski
    CodeSmith Tools, LLC. Software Development Engineer
    Blog: http://windowscoding.com/blogs/blake/
    .NetTiers team | Visit http://www.nettiers.net

All Replies
  • When CodeSmith tries to discover the type of resultset returned from stored procs, it does so by issuing the SET FMTONLY ON command to SQL Server.  When this option is activated, the procedures are run with limited rights and therefore your call to an external stored procedure is not executed.  See this thread for more info:

    http://community.codesmithtools.com/forums/permalink/17330/17330/ShowThread.aspx#17330

    You can check for a user_name() is null to determine if this is the case and return a sample record so that the proper metadata is returned to CodeSmith.  For example, you can add this to the begining of your procedure:

    if user_name() is null
    begin
        select AddressId, Info, Unit, Lot, Number, Street, Suburb, StateProvince, Postcode from Address where 1=0
        return
    end

    Hope that helps!


    Bobby Diaz ------------------------------------------ Member of the .NetTiers team http://www.nettiers.com ------------------------------------------
  • That fixed it!
    Apreciate your help Bobby!

    Dean

  • I have a very similar sp, but wen i try to generate the code with nettier codesmith tell me that the sp is not in the expected format.

    I really don't know what to do...
     

    elfederiko
  • Still seeing this issue with Net Tiers 2.2 ... is this a 'live one'?

    Returning a dummy parameter list doesn't fix it for me...

  • I used the dummy code and I am now returning an IDataReader.  I also want this method to be able to return a @TotalRowCount variable as well.  The value is being set properly in the SPROC and the SQL Profiler looks like this:

    declare @p6 int
    set @p6=33
    exec dbo._Listing_GetSearchResults @VisitorID='D35E2D4B-CA34-4DBB-A0C0-47DA1C8DAF9F',@WhereClause='Listing.BoardID = 1 AND AreaID IN(480)',@OrderBy='listprice DESC',@PageIndex=0,@PageSize=20,@TotalRowCount=@p6 output
    select @p6

    Again, it IS populating the DataReader, but is not returning the @TotalRowCount... which, in this case is 33.

  • Hello,

    Please take a look at the following thread for more information: http://community.codesmithtools.com/support_forums1/f/9/t/11437.aspx. The key is to check the result set being returned by the following queries. The NULL Statements need to be added for each parameter of your stored procedure.

    SET FMTONLY ON
    
    EXEC [dbo].[stored_procedure]
    
        NULL
    
    SET FMTONLY OFF
    
    -- Table Valued Function..
    
    SET FMTONLY ON
    
    SELECT * FROM [dbo].[sql_function] (NULL)
    
    SET FMTONLY OFF

    I would check to see if each parameter is null and if so return a set of columns like select * from dbo.tablename... Sometimes, SQL Server has a hard time parsing the results. If this is the case, this is the only work around to solving this issue. As with the post above, FMTONLY is somewhat limited and will evaluate all queries that are inside of if logic..

    If configuring your stored procedure and debugging it doesn't pan out (very rarely is this the case Sad). The only way to modify what is generated is to manually update the templates to account for this stored procedure.

    @GRIER, you would need to specify the TotalRowCount as an output parameter in the actual stored procedure parameter definition.

     

    Thanks

    -Blake Niemyjski

     

    Blake Niemyjski
    CodeSmith Tools, LLC. Software Development Engineer
    Blog: http://windowscoding.com/blogs/blake/
    .NetTiers team | Visit http://www.nettiers.net

Page 1 of 1 (7 items)