Welcome to the CodeSmith Community!

Custom Stored Procedure Does not return Dataset (anymore)


A description has not yet been added to this group.

Custom Stored Procedure Does not return Dataset (anymore)

  • rated by 0 users
  • This post has 6 Replies |
  • Hi,

    I have a problem with a custom stored procedure. It does not return Dataset anymore (worked fine until now) when I am calling it from the Service of the entity which belongs.

    Stored Procedure :

    CREATE PROCEDURE [dbo].[usp_Material_FindMaterial_Like]
                     @Code as nvarchar(50)
    SELECT    MBarCode.BarCode, Material.Code, Material.Descript, MBarCode.Qty, uvVats.VatDescript, uvVats.VatPercentage,
            uvVats.AlterVatCode, uvVats.AlterVatDescript, uvVats.AlterVatPercentage, MBarCode.SizeCode , MBarCode.ColorCode
    FROM    Material
        INNER JOIN uvVats ON Material.VatCode = uvVats.VatCode
        INNER JOIN ( select MaterialCode, BarCode, Qty , ColorCode, SizeCode
                     from MaterialBarCodes
                     where BarCode Like @Code OR MaterialCode Like @Code
                   ) as MBarCode ON Material.Code = MBarCode.MaterialCode
    Where IsActive = 1
    ORDER BY MBarCode.BarCode, Material.Code   

    Now returns void. What changed?


    Sex is like programming; one mistake and you must support it forever...
  •  This normally indicates that your SP is broken in some way.



    ------------------------------------------------- Member of the .NetTiers team -------------------------------------------------
  • Yes I Think you are right EmbarrassedEmbarrassed 

    Sex is like programming; one mistake and you must support it forever...
  • Actually this is a problem. I'm having the same issue and there is nothing wrong with the stored procedure. The issue is in particular coming from when building a sql string and then doing

    exec sp_executesql @sql just like they do in _GetPaged stored procedures.

    I found that in file TemplateLib\CommonSqlCode.cs function GetReturnCustomProcReturnType that the code has changed from the previous versions. They added another if test in the else "else if (command.CommandResults != null && command.CommandResults.Count > 0)". Where as before it was just plain else.

    I've had to add to bottom of my custom stored procure select 'dummy' to fake a table. I also tried Select @@RowCount, but after generating the code the stored procedure had been re-written and Select @@RowCount has been deleted.

    Anybody else having issues or have another solution besides adding select 'dummy' to bottom of stored procedure? I could edit the Template file, but don't want to.

    Thanks ... Chad


  • Check out this link for more information about this issue:


    It has to with how CodeSmith executes the stored procedure to determine the result set.

    Also, what version of the templates are you using (check changes.log file)?  The reason I ask is that there used to be bug where custom procedures were being dropped and re-created when the templates were executed.  This was fixed a while back, so you may want to update to a new build of netTiers.

    Ben Johnson
     Member of the .NetTiers team
     Visit http://www.nettiers.com

  • We are using the latest public release,  v2.0.1.471. I would rather stick with official public release then get nightly builds, but if this has been fixed or straight forward work around then I would consider using a latest nightly build.

    It doesn't sound like there is anything really to  'fix' due to the limitations of SET FMTONLY ON not being able to intepret dynamic sql string. So perhaps another solution would be to add another check for maybe this naming convention _<TableName>_DS_<GetMethod> when a _DS_ is found then a dataset is just returned. We modified a older version of the templates to do this about 2 years ago. With version 2(?) of the templates it automatically detected dataset or TList which worked for us, so we didn't re-modify the template again. So without doing some hack like adding Select @@RowCount or Select 'Dummy' to bottom of procedure we might have to modify templates to check for _DS_. Of course I rather not have to do that because we'd continually have to modify templates until something was put in public release.

    Anybody had any luck with getting your own customizations that might make sense for the general public added into the public release? I've also added a very cool feature that I'd love to see in there. I added a automatic compile the generated code and then copy to a shared location for other developers to reference the dlls. I added a property to save the path to the shared location and a true/false to even do it.

    Here is another good article that describes the dataset issues: http://community.codesmithtools.com/forums/p/2806/17330.aspx#17330

    Thanks ... Chad

  • Actually the other option to 'fix' this would be to change the Else block in TemplateLib\CommonSqlCode.cs function GetReturnCustomProcReturnType. Remove "else if (command.CommandResults != null && command.CommandResults.Count > 0)" and just go back to "else". But I'm sure that would break something somebody is trying to use a void for.

    But this is why it hasn't been a problem for us in the older versions of the templates.

    Still NetTiers templates are AWESOME !!!!!

Page 1 of 1 (7 items)