Custom Stored Procedure Does not return Dataset (anymore)

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)
AS
BEGIN
    SET NOCOUNT ON;
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   
END

Now returns void. What changed?

 George
 

 This normally indicates that your SP is broken in some way.

hth

swin 

Yes I Think you are right  

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:

http://community.codesmithtools.com/forums/p/4695/19044.aspx#19044

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.

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 !!!!!