Re: custom stored procedure returning void isntead a dataset

You need to make sure that when the stored procedure is executed with Null for each parameter, that it returns a result set. 

Nettiers does a mock execute on the stored procedure passing in all nulls as parameters to get the Result schema.

jeff

i will try with that and get u back, thanks

well i tested the sp with all the parameters null and it returns all the records of the db, so it is fine like this,but still getting the methods with void, here is the new sp, and the query:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_c_paciente_FindCiudadanoPaciente]
    -- Add the parameters for the stored procedure here
    @WhereClause varchar (2000) = NULL,
    @OrderBy varchar (2000) = NULL,
    @PageIndex int = NULL,
    @PageSize int = NULL 
   
AS
BEGIN
    IF @PageIndex IS NULL
    SET @PageIndex = 0
   
    IF @PageSize IS NULL
    SET @PageSize = 400
   
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
   
    -- Set the page bounds
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageLowerBound + @PageSize
   
    IF (@OrderBy IS NULL OR LEN(@OrderBy) < 1)
    BEGIN
        -- default order by id_ciudadano
        SET @OrderBy = '[c_paciente].id_paciente'
    END

    -- SQL Server 2005 Paging
    DECLARE @SQL AS nvarchar(MAX)
    SET @SQL = 'WITH PageIndex AS ('
    SET @SQL = @SQL + ' SELECT'
    IF @PageSize > 0
    BEGIN
        SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
    END
    SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
    SET @SQL = @SQL + ' ,[c_paciente].id_paciente'
    SET @SQL = @SQL + ' ,[c_paciente].id_grupo_sanguineo'
    SET @SQL = @SQL + ' ,[c_paciente].id_factor_sanguineo'
    SET @SQL = @SQL + ' ,[c_paciente].nombre_centro_trabajo'
    SET @SQL = @SQL + ' ,[c_paciente].telf_centro_trabajo'
    SET @SQL = @SQL + ' ,[c_paciente].email'
    SET @SQL = @SQL + ' ,[c_paciente].id_tipo_movimiento'
    SET @SQL = @SQL + ' ,[c_paciente].fecha_movimiento'
    SET @SQL = @SQL + ' FROM [c_paciente] INNER JOIN [c_ciudadano] ON [c_paciente].id_paciente = [c_ciudadano].id_ciudadano '
    IF (@WhereClause IS NOT NULL AND LEN(@WhereClause) > 0)
    BEGIN
        SET @SQL = @SQL + ' WHERE ' + @WhereClause
    END
    SET @SQL = @SQL + ' ) SELECT'
    SET @SQL = @SQL + ' id_paciente,'
    SET @SQL = @SQL + ' id_grupo_sanguineo,'
    SET @SQL = @SQL + ' id_factor_sanguineo,'
    SET @SQL = @SQL + ' nombre_centro_trabajo,'
    SET @SQL = @SQL + ' telf_centro_trabajo,'
    SET @SQL = @SQL + ' email,'
    SET @SQL = @SQL + ' id_tipo_movimiento,'
    SET @SQL = @SQL + ' fecha_movimiento'
    SET @SQL = @SQL + ' FROM PageIndex'
    SET @SQL = @SQL + ' WHERE RowIndex > ' + CONVERT(nvarchar, @PageLowerBound)
    IF @PageSize > 0
    BEGIN
        SET @SQL = @SQL + ' AND RowIndex <= ' + CONVERT(nvarchar, @PageUpperBound)
    END
    SET @SQL = @SQL + ' ORDER BY ' + 'id_paciente'
    EXEC sp_executesql @SQL
   
    -- get row count
    SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
    SET @SQL = @SQL + ' FROM [c_paciente] INNER JOIN [c_ciudadano] ON [c_paciente].id_paciente = [c_ciudadano].id_ciudadano '
    IF (@WhereClause IS NOT NULL AND LEN(@WhereClause) > 0)
    BEGIN
        SET @SQL = @SQL + ' WHERE ' + @WhereClause
    END
    EXEC sp_executesql @SQL
    PRINT @SQL
  
END

the execution of the sp:

DECLARE    @return_value int

EXEC    @return_value = [dbo].[usp_c_paciente_FindCiudadanoPaciente]
        @WhereClause = NULL,
        @OrderBy = NULL,
        @PageIndex = NULL,
        @PageSize = NULL

SELECT    'Return Value' = @return_value

GO

and the results:
(2 row(s) affected)

(1 row(s) affected)
SELECT COUNT(*) AS TotalRowCount FROM [c_paciente] INNER JOIN [c_ciudadano] ON [c_paciente].id_paciente = [c_ciudadano].id_ciudadano

(1 row(s) affected)

it returned all the records in the table c_paciente

so this is not the problem, any idea?

So, in custom procs, by default, if there is more than one result set, it returns a dataset.

You have 2 results in this stored procedure, the SELECT COUNT(*).  This is a waisted statement.  It isn't used in Nettiers at all.

Two solutions:

1.  Get rid of the Select Count

2.  There is an option in 07.  Crud - Advanced that says AllowCustomProcMultipleResults.  Set that to try and it will ignore all other result sets other than the first.

jeff

i tried the second option and it didn't work, continues generating the void method, let me try with the first option, but i was seeing the  nettiers sp, and all getpaged have this way.

trying first solution ....... and get back

hey i did the first but the same, here is the new sp:

ALTER PROCEDURE [dbo].[usp_c_paciente_FindCiudadanoPaciente]
    -- Add the parameters for the stored procedure here
    @WhereClause varchar (2000) = NULL,
    @OrderBy varchar (2000) = NULL,
    @PageIndex int = NULL,
    @PageSize int = NULL 
   
AS
BEGIN
    IF @PageIndex IS NULL
    SET @PageIndex = 0
   
    IF @PageSize IS NULL
    SET @PageSize = 400
   
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
   
    -- Set the page bounds
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageLowerBound + @PageSize
   
    IF (@OrderBy IS NULL OR LEN(@OrderBy) < 1)
    BEGIN
        -- default order by id_ciudadano
        SET @OrderBy = '[id_paciente]'
    END

    -- SQL Server 2005 Paging
    DECLARE @SQL AS nvarchar(MAX)
    SET @SQL = 'WITH PageIndex AS ('
    SET @SQL = @SQL + ' SELECT'
    IF @PageSize > 0
    BEGIN
        SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
    END
    SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
    SET @SQL = @SQL + ' ,[c_paciente].id_paciente'
    SET @SQL = @SQL + ' ,[c_paciente].id_grupo_sanguineo'
    SET @SQL = @SQL + ' ,[c_paciente].id_factor_sanguineo'
    SET @SQL = @SQL + ' ,[c_paciente].nombre_centro_trabajo'
    SET @SQL = @SQL + ' ,[c_paciente].telf_centro_trabajo'
    SET @SQL = @SQL + ' ,[c_paciente].email'
    SET @SQL = @SQL + ' ,[c_paciente].id_tipo_movimiento'
    SET @SQL = @SQL + ' ,[c_paciente].fecha_movimiento'
    SET @SQL = @SQL + ' FROM [c_paciente] INNER JOIN [c_ciudadano] ON [c_paciente].id_paciente = [c_ciudadano].id_ciudadano '
    IF (@WhereClause IS NOT NULL AND LEN(@WhereClause) > 0)
    BEGIN
        SET @SQL = @SQL + ' WHERE ' + @WhereClause
    END
    SET @SQL = @SQL + ' GROUP BY '
    SET @SQL = @SQL + '  [c_paciente].id_paciente'
    SET @SQL = @SQL + ' ,[c_paciente].id_grupo_sanguineo'
    SET @SQL = @SQL + ' ,[c_paciente].id_factor_sanguineo'
    SET @SQL = @SQL + ' ,[c_paciente].nombre_centro_trabajo'
    SET @SQL = @SQL + ' ,[c_paciente].telf_centro_trabajo'
    SET @SQL = @SQL + ' ,[c_paciente].email'
    SET @SQL = @SQL + ' ,[c_paciente].id_tipo_movimiento'
    SET @SQL = @SQL + ' ,[c_paciente].fecha_movimiento'
   
    SET @SQL = @SQL + ' ) SELECT'
    SET @SQL = @SQL + ' id_paciente,'
    SET @SQL = @SQL + ' id_grupo_sanguineo,'
    SET @SQL = @SQL + ' id_factor_sanguineo,'
    SET @SQL = @SQL + ' nombre_centro_trabajo,'
    SET @SQL = @SQL + ' telf_centro_trabajo,'
    SET @SQL = @SQL + ' email,'
    SET @SQL = @SQL + ' id_tipo_movimiento,'
    SET @SQL = @SQL + ' fecha_movimiento'
    SET @SQL = @SQL + ' FROM PageIndex'
    SET @SQL = @SQL + ' WHERE RowIndex > ' + CONVERT(nvarchar, @PageLowerBound)
    IF @PageSize > 0
    BEGIN
        SET @SQL = @SQL + ' AND RowIndex <= ' + CONVERT(nvarchar, @PageUpperBound)
    END
    SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
    EXEC sp_executesql @SQL
   
END

i tested it with all the parameters in null and it returns fine. any help.

any help with this?

Hello,

Can you post a snippet of what it generates for this as well as what your csp options are for ignore extra result sets and your custom stored procedures pattern.

Thanks

-Blake Niemyjski

Can you try executing the proc like this:

set fmt_only on
go

EXEC proc NULL, NULL, NULL

set fmt_only off
go

Try that and see if you get a result set.

jeff

when i tried to execute this:

USE [Gerisoft(09-12-21)]
GO
set fmt_only on
go

EXEC [dbo].[usp_c_paciente_FindCiudadanoPaciente] NULL, NULL, NULL, NULL

set fmt_only off
go

i recieved the following error:

Msg 195, Level 15, State 5, Line 1
'fmt_only' is not a recognized SET option.

Msg 195, Level 15, State 5, Line 4
'fmt_only' is not a recognized SET option.

??

there are all my options of generation, there is the csp, the database script and the mapping file, and here is the generated code:

#region usp_c_paciente_FindCiudadanoPaciente
                   
        /// <summary>
        ///    This method wraps the 'usp_c_paciente_FindCiudadanoPaciente' stored procedure.
        /// </summary>   
        /// <param name="whereClause"> A <c>System.String</c> instance.</param>
        /// <param name="orderBy"> A <c>System.String</c> instance.</param>
        /// <param name="pageIndex"> A <c>System.Int32?</c> instance.</param>
        /// <param name="pageSize"> A <c>System.Int32?</c> instance.</param>
        /// <param name="start">Row number at which to start reading.</param>
        /// <param name="pageLength">Number of rows to return.</param>
        /// <param name="transactionManager"><see cref="TransactionManager"/> object.</param>
        /// <remark>This method is generated from a stored procedure.</remark>
        public override void FindCiudadanoPaciente(TransactionManager transactionManager, int start, int pageLength , System.String whereClause, System.String orderBy, System.Int32? pageIndex, System.Int32? pageSize)
        {
            SqlDatabase database = new SqlDatabase(this._connectionString);
            DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.usp_c_paciente_FindCiudadanoPaciente", true);
           
            database.AddInParameter(commandWrapper, "@WhereClause", DbType.AnsiString,  whereClause );
            database.AddInParameter(commandWrapper, "@OrderBy", DbType.AnsiString,  orderBy );
            database.AddInParameter(commandWrapper, "@PageIndex", DbType.Int32,  pageIndex );
            database.AddInParameter(commandWrapper, "@PageSize", DbType.Int32,  pageSize );
   
           
            //Provider Data Requesting Command Event
            OnDataRequesting(new CommandEventArgs(commandWrapper, "FindCiudadanoPaciente", (IEntity)null));

            if (transactionManager != null)
            {   
                Utility.ExecuteNonQuery(transactionManager, commandWrapper );
            }
            else
            {
                Utility.ExecuteNonQuery(database, commandWrapper);
            }
                       
            //Provider Data Requested Command Event
            OnDataRequested(new CommandEventArgs(commandWrapper, "FindCiudadanoPaciente", (IEntity)null));


               
                return;
        }
        #endregion
        #endregion

any help with that?

Hello,

Try.

SET FMTONLY ON;
GO
Exec Procname.
GO
SET FMTONLY OFF;

Thanks

Blake Niemyjski

yep its execute fine but without results, maybe for this reason are generating a void method, but why? it is the same of all the getpaged methods, even more simple, any quick solution for that? can u reproduce it?

i did this in my code and it works fine without problem, to continues with my work, and following the same line of the nettiers generation:

public override TList<Paciente> FindCiudadanoPaciente(TransactionManager transactionManager, System.String whereClause, System.String orderBy, int start, int pageLength)
        {
            SqlDatabase database = new SqlDatabase(this._connectionString);
            DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.usp_c_paciente_FindCiudadanoPaciente", true);

            database.AddInParameter(commandWrapper, "@WhereClause", DbType.AnsiString, whereClause);
            database.AddInParameter(commandWrapper, "@OrderBy", DbType.AnsiString, orderBy);
            database.AddInParameter(commandWrapper, "@PageIndex", DbType.Int32, start);
            database.AddInParameter(commandWrapper, "@PageSize", DbType.Int32, pageLength);

            IDataReader reader = null;

            //Create Collection
            TList<Paciente> rows = new TList<Paciente>();

            try
            {
                //Provider Data Requesting Command Event
                OnDataRequesting(new CommandEventArgs(commandWrapper, "FindCiudadanoPaciente", (IEntity)null));

                if (transactionManager != null)
                {
                    reader = Utility.ExecuteReader(transactionManager, commandWrapper);
                }
                else
                {
                    reader = Utility.ExecuteReader(database, commandWrapper);
                }

                Fill(reader, rows, start, pageLength);
               
                //Provider Data Requested Command Event
                OnDataRequested(new CommandEventArgs(commandWrapper, "FindCiudadanoPaciente", (IEntity)null));
            }
            finally
            {
                if (reader != null)
                    reader.Close();

                commandWrapper = null;
            }


            return rows;
        }

it is something like i was  expecting from the generation