Custom sp_Entry_GetActiveList is not in the expected format.

Hi,

I've changed the proc as suggested above but it still gives the error. It now looks like:

 CREATE PROCEDURE [dbo].[_Entry_GetActiveList]

@totalRecords INT OUTPUT

AS

SELECT
     [Id],
     [Headword],
     [LangAbbrev],
     [WhoUpdated],
     [WhenUpdated]
    FROM
     dbo.[Entry]
    WHERE Active=1

SET @totalRecords = @@ROWCOUNT

GO

Is there any documentation on the expected format?

thanks

Dave

Dave,

I created a test table to approximate yours using teh following

CREATE TABLE [dbo].[Entry](
 [Id]int IDENTITY(1,1) NOT NULL,
     [Headword] varchar(50) NOT NULL,
     [LangAbbrev] varchar(5) NOT NULL,
     [WhoUpdated] varchar(50) NOT NULL,
     [WhenUpdated] datetime,
     [Active] bit

CONSTRAINT [PK_Entry] PRIMARY KEY CLUSTERED
(
 [Id] ASC
)
)
GO

I then created your proc using your code above (including leading space before CREATE).

I did a gen (CS 3.2, SVN533, Sql2005) and I got the same error your did.

I then dropped your proc and recreated it BUT removing the leading space from before the CREATE first.

I then did a regen and it worked fine. 

 public virtual  DataSet GetActiveList(ref System.Int32? totalRecords)
 {
     return GetActiveList( ref totalRecords, 0, defaultMaxRecords );
 }

Note that you'll get a DataSet back rather than a TList unless you specify all of the columns in your SP the same as your table (i.e. currently your SP doesn't return the "Active"column)

It seems the leading space is throwing something out so you must make sure that the "CREATE PROCEDURE" has no leading spaces and only 1 space between the words.

hth

swin 

 

Robert Hinojosa:

This is using dynamic SQL, (Execute(@Select + @Where)) there is no meta-data that can be found when running this. You are dynamically passing in a dynamic string every time you run this procedure, so there's no possible way to determine what kind of meta-data your procedure is going to return.

Hi Robert I try This but still don't work, any suggestion?

Thanks in advance

this is the new sp

set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[_Negocios_AdvancedSearch]
(
@Neg_NegocioID varchar(10)= null,
@Neg_Nombre varchar(100)= null,
@Neg_Calle varchar(80)= null,
@Neg_Altura varchar(8)= null,
@Neg_BarrioID varchar(8)= null,
@Neg_Telefono1 varchar(20)= null,
@Neg_Delivery varchar(2)= null,
@Neg_Reservas varchar(2)= null,
@Neg_DiaAtencionID varchar(8)= null,
@Neg_HorarioAtencionID varchar(8)= null,
@Neg_Activo varchar(8)= null,
@Rne_RubroID varchar(8)= null

) AS

IF @Rne_RubroID <> "" and @Rne_RubroID is not null
Begin
    SELECT     [Neg_Nombre],
            [Neg_Calle],
            [Neg_Altura],
            [Neg_BarrioID],
            [Neg_Telefono1],
            [Neg_Delivery],
            [Neg_Reservas],
            [Neg_DiaAtencionID],
            [Neg_HorarioAtencionID]
    FROM dbo.[Negocios], dbo.[RubrosNegocio]
    WHERE    Neg_NegocioID = COALESCE(@Neg_NegocioID,Neg_NegocioID) AND
            Neg_Nombre like COALESCE(@Neg_Nombre,Neg_Nombre) AND
            Neg_Calle like COALESCE(@Neg_Calle,Neg_Calle) AND
            Neg_Altura = COALESCE(@Neg_Altura,Neg_Altura) AND
            Neg_BarrioID = COALESCE(@Neg_BarrioID,Neg_BarrioID) AND
            Neg_Telefono1 = COALESCE(@Neg_Telefono1,Neg_Telefono1) AND
            Neg_Delivery = COALESCE(@Neg_Delivery,Neg_Delivery) AND
            Neg_Reservas = COALESCE(@Neg_Reservas,Neg_Reservas) AND
            Neg_DiaAtencionID = COALESCE(@Neg_DiaAtencionID,Neg_DiaAtencionID) AND
            Neg_HorarioAtencionID = COALESCE(@Neg_HorarioAtencionID,Neg_HorarioAtencionID) AND
            Neg_Activo = COALESCE(@Neg_Activo,Neg_Activo) AND
            Rne_RubroID = COALESCE(@Rne_RubroID,Rne_RubroID) AND
            Rne_NegocioID = Neg_NegocioID
End
Else
Begin
    SELECT  [Neg_Nombre],
            [Neg_Calle],
            [Neg_Altura],
            [Neg_BarrioID],
            [Neg_Telefono1],
            [Neg_Delivery],
            [Neg_Reservas],
            [Neg_DiaAtencionID],
            [Neg_HorarioAtencionID]
    FROM dbo.[Negocios]
    WHERE Neg_NegocioID = COALESCE(@Neg_NegocioID,Neg_NegocioID) AND
            Neg_Nombre like COALESCE(@Neg_Nombre,Neg_Nombre) AND
            Neg_Calle like COALESCE(@Neg_Calle,Neg_Calle) AND
            Neg_Altura = COALESCE(@Neg_Altura,Neg_Altura) AND
            Neg_BarrioID = COALESCE(@Neg_BarrioID,Neg_BarrioID) AND
            Neg_Telefono1 = COALESCE(@Neg_Telefono1,Neg_Telefono1) AND
            Neg_Delivery = COALESCE(@Neg_Delivery,Neg_Delivery) AND
            Neg_Reservas = COALESCE(@Neg_Reservas,Neg_Reservas) AND
            Neg_DiaAtencionID = COALESCE(@Neg_DiaAtencionID,Neg_DiaAtencionID) AND
            Neg_HorarioAtencionID = COALESCE(@Neg_HorarioAtencionID,Neg_HorarioAtencionID) AND
            Neg_Activo = COALESCE(@Neg_Activo,Neg_Activo)
End   
Select @@ROWCOUNT
SET NOCOUNT ON
RETURN

swin,

Thanks for that, it's working ok now. Sorry for the delayed reply, I've been away from work. 

Thanks again

Dave Risley

Dave - glad its working for you now.

elfederiko - we have a search function similar in concept to you and we use a temp table variable like so...

declare @SearchResults TABLE
(
  ProductId int,
  AnotherColumn
) 

IF (somelogic)
BEGIN
INSERT INTO @SearchResults
    SELECT ProductId,AnotherColumn FROM MyTable1
END
ELSE
BEGIN
INSERT INTO @SearchResults
    SELECT ProductId,AnotherColumn FROM MyTable2 LEFT JOIN MyTable3 on mycol1=mycol2
END

SELECT * FROM @SearchResults

This way CodeSmith knows what the resultset will be and should be happy to generate your methods.

hth

swin

 

I was having a problem similar to this with one of my stored procedures.  Here is what I did to fix it:

1. Drop the existing proc.
2. Put the 'AS' clause on the second line
3. Removed all leading AND trailing whitespace on line one.  On line one I had: "CREATE PROCEDURE MyProcedure"
4. Ran the execute to re-create the proc.

This fixed my problem.  Thanks for the help!