Custom Stored Proc (well formed) returns a dataset

(Procs below)

I wrote a custom stored proc for the framework and I needed (thought I needed) an extra field in the result set. I added it to the database and did a gen. As expected, the method returns a dataset.

I later realized that none of the apps invoking the method required the extra field, so I thought it would be better to return a TList. So I made sure the Custom proc returned the same columns as the generated procs. I deleted the mapping.config file altogether and regenned. The method created still returns a Dataset instead of a TList of ShowCategories.

Please let me know what I'm doing wrong here.

Thanks

Paul

.netTiers proc:

CREATE PROCEDURE dbo.ShowCategory_Get_List

AS


                SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
                SELECT
                    [ShowCategoryID], [DivId], [Title], [Sort], [Grid], [SessionID], [RowVer]
                FROM
                    [dbo].[ShowCategory]
                   
                SELECT @@ROWCOUNT
           

GO
GRANT EXEC ON dbo.ShowCategory_Get_List TO B2BUser,B2BUser

GO

Custom Proc:

CREATE proc dbo.cstm_showcategory_SelectHavingScreeners
@DivID INT,
@UserID INT,
@Active BIT
as


DECLARE @date smalldatetime
SET @date = dbo.ufFormatDate(getdate())

DECLARE @permissionLevel Int
SET @permissionLevel = dbo.ufScreenerPermissionLevelForUser(@userId, @date)

DECLARE    @userIsAdmin Int
SET @userIsAdmin=dbo.ufIsAdmin(@divid, @userid)


IF @userIsAdmin = 1

    SELECT DISTINCT sc.ShowCategoryID, sc.DivId, sc.Title, sc.Sort, sc.Grid, sc.SessionID,
                    sc.RowVer
    FROM ShowCategory sc
    INNER JOIN Show s ON s.ShowCategoryID = sc.ShowCategoryID
    INNER JOIN ScreenerXShow sxs on sxs.ShowID = s.ShowID
    INNER JOIN Screener scr ON scr.screenerID = sxs.ScreenerID
    WHERE s.DivID = @DivID AND scr.Active = @Active
    ORDER BY sc.Sort, sc.Title
ELSE
    SELECT DISTINCT sc.ShowCategoryID, sc.DivId, sc.Title, sc.Sort, sc.Grid, sc.SessionID, sc.RowVer
    FROM ShowCategory sc
    INNER JOIN Show s ON s.ShowCategoryID = sc.ShowCategoryID
    INNER JOIN ScreenerXShow sxs on sxs.ShowID = s.ShowID
    INNER JOIN Screener scr ON scr.screenerID = sxs.ScreenerID
    INNER JOIN dbo.ufAllShowsForUser(@divid, @userid, @date, 1) ufShows on ufShows.showid = sxs.ShowID
    WHERE s.DivID = @DivID
            AND scr.Active = @Active
            AND scr.PermissionLevel <= @permissionLevel
    ORDER BY sc.Sort, sc.Title

SELECT @@ROWCOUNT

go
grant execute on dbo.cstm_showcategory_SelectHavingScreeners to b2brole
go

When rendering the template its possible that it may be picking up your multiple selects as multiple result sets.  Honestly I don't know if the SELECT @@ROWCOUNT counts as a result set in SchemaExplorer's eyes.  To confirm you could alter your stroed proc to remove the If and possible the SELECT @@Rowcount.

Even if you get a DataSet you should be able to convert this to a Tlist.  If you use DataReader as your non matching return type its even easier b/c you don't need the first step.  Try using the CreateDataReader on your DataSet and then sending the data reader to the appropriate provider (seems like that's ShowCategory for you)

Check this post:

http://community.codesmithtools.com/forums/p/3501/14523.aspx#14523

It's kinda old but might solve your problem. I've used it before and worked just fine.

hth

Thanks guys, your posts have helped clarify things, but I still can't get it to operate as expected. I have changed the proc to the one below.

When I run the following:

SET FMTONLY ON
exec cstm_showcategory_SelectHavingScreeners 1,97,1

I get the one result and the @@rowcount result.

When I run the gen it still defaults to the Dataset fallback. rhettc pointed out that I can then probably get the provider to create a TList for me from the dataset, but I wanted to work this out for future development. If .net tiers is going to do this for every custom stored proc we create, it just adds an unnecessary step to each call to our procs.

Could it also be a caching issue?

Should I delete the mapping files before the gen?

Am I running the the proc correctly with "SET FMTONLY ON?"

Thanks Again,

Paul Perrick

CBS Paramount Television Distribution

DECLARE @sc TABLE (scId Int)


IF @userIsAdmin = 1

    INSERT @sc (scId)
    SELECT DISTINCT sc.ShowCategoryID
    FROM ShowCategory sc
    INNER JOIN Show s ON s.ShowCategoryID = sc.ShowCategoryID
    INNER JOIN ScreenerXShow sxs on sxs.ShowID = s.ShowID
    INNER JOIN Screener scr ON scr.screenerID = sxs.ScreenerID
    WHERE s.DivID = @DivID AND scr.Active = @Active
ELSE
    INSERT @sc (scId)
    SELECT DISTINCT sc.ShowCategoryID
    FROM ShowCategory sc
    INNER JOIN Show s ON s.ShowCategoryID = sc.ShowCategoryID
    INNER JOIN ScreenerXShow sxs on sxs.ShowID = s.ShowID
    INNER JOIN Screener scr ON scr.screenerID = sxs.ScreenerID
    INNER JOIN dbo.ufAllShowsForUser(@divid, @userid, @date, 1) ufShows on ufShows.showid = sxs.ShowID
    WHERE s.DivID = @DivID
            AND scr.Active = @Active
            AND scr.PermissionLevel <= @permissionLevel



SELECT ShowCategoryID, DivId, Title, Sort, Grid, SessionID, RowVer
    FROM ShowCategory JOIN @sc sc2 ON sc2.scId = ShowCategory.ShowCategoryID
    ORDER BY Sort, Title
SELECT @@ROWCOUNT

I think it is the multiple result sets that are causing it to not work.  Why are you returning the @@rowcount?  Custom procs that return multiple results will return a dataset.  This was a recent change.  Nettiers only looks at the one result when returning a custom proc and a Tlist.  It doesn't have a clue what to do with the @@rowcount.

If you get rid of that, it should solve your problem.

jeff

Also, Nettiers sets all parameters to Null to get the result.  So test to make sure that the proc returns the correct result when passing in all nulls.

jeff

OK it was the @@rowcount

All is good with the world.

Thanks guys

Yeah, I'm not sure about the @@Rowcount, 'cause I lways use the rowcount in my procedures... netTiers validates the fist returned dataset against the table columns to set the return type...

Theres some discussion going about this issue  int the bug tracker, you should check it out.