Stored procedure works with datareader but not with dataset

[Le Fletan]

This is gonna be a long and tuff (hope not) one !

Hi everyone, please if you help me send me a hint ! I have been on
that on full time for 8 hours...Here it is:

I have a dynamic stored procedure (see code below) that builds a
Transact-SQL statement an executes it. In that Stored procedure there
are IF statements but it seems like the IF statements are bypassed
when the stored procedure is executed. But the content of all IF and
ELSE blocks are executed

If you look in the code, you'll see that those line are all executed
-- Email
SET @ORDERBY_STR = @ORDERBY_STR + 'U.email_USAGER, U.nom_USAGER,
U.prenom_USAGER '

-- Prenom
SET @ORDERBY_STR = @ORDERBY_STR + 'U.prenom_USAGER, U.nom_USAGER,
U.email_USAGER '

-- Nom (1)
SET @ORDERBY_STR = @ORDERBY_STR + 'U.nom_USAGER, U.prenom_USAGER,
U.email_USAGER '

but they should not, only one of them should...I found that out
because the way it is, it throws an error "Invalid syntax near 'U'" at
the beginning of the 2nd SET ORDERBY_STR statement. if I add a coma
(,) at the end of the 1st SET ORDERBY_STR statement, I get an error at
the same place, but it's a "A column has been specified more than once
in the order by list..." error. Because the 3 statements contain the
same fields, only in a different order, so if the 1st and 2nd are
executed, I get duplicates in my order by statement and this should
throw an error, which it does...

So anyway, if you have any idea, ANY, please write me an e-mail at
(e-mail address removed)

KEEP in mind, this behaviour does not occur in SQL Query analyzer or
when I call the stored procedure to get an SqlDataReader, only when I
call a DataSet...

Here is the C# code that calls the stored procedure followed by the
stored procedure it self. (don't worry about the way I call my
dataset, it's a data access component I have done for myself, it's
full proof I've been using it for a year and a half, the problem is
not there)

SORRY IF IT WAS SO LONG !

/***************** C# CODE **********************/

// Fills the parameter collection of the SqlCommand and gets the
DataSet
public DataSet mGetListUserTous_DS( string pIdLangueAffichage,
string pCritereDeRechercher,
eTypeRecherche pTypeDeRecherche,
eRechercherParmis pRechercherParmis,
eClasserPar pClasserPar,
bool pTriAscendant )
{

cConnectionDb lDb = new cConnectionDb( false,
eChoixConnection.ConnectionString );

lDb.mCreateParametre( "@id_LANGUE", SqlDbType.Char, 2,
ParameterDirection.Input, pIdLangueAffichage );
lDb.mCreateParametre( "@critere", SqlDbType.NVarChar, 255,
ParameterDirection.Input, pCritereDeRechercher );
lDb.mCreateParametre( "@typeRecherche", SqlDbType.Int, 4,
ParameterDirection.Input, (int)pTypeDeRecherche );
lDb.mCreateParametre( "@rechercherParmis", SqlDbType.Int, 4,
ParameterDirection.Input, (int)pRechercherParmis );
lDb.mCreateParametre( "@classerPar", SqlDbType.Int, 4,
ParameterDirection.Input, (int)pClasserPar );
lDb.mCreateParametre( "@triAscendant", SqlDbType.Bit, 1,
ParameterDirection.Input, pTriAscendant );

return lDb.mGetDataSet( "User_Liste", true );

}

// Gets a DataSet
public DataSet mGetDataSet( string pStoredProcedureName, bool
pCloseConnectionAtTheEnd )
{
SqlDataAdapter lAdapter = new SqlDataAdapter( aSqlCommand );

aSqlCommand.CommandType = CommandType.StoredProcedure;
aSqlCommand.CommandText = pStoredProcedureName;

DataSet lDataSet = new DataSet();

//Exécute la requête
try
{
mOpenConnection();

lAdapter.FillSchema( lDataSet , SchemaType.Mapped );
lAdapter.Fill( lDataSet, "Table" );
}
catch( SqlException erreur )
{
throw erreur;
}
finally
{
if( pCloseConnectionAtTheEnd )
{
mCloseConnection();
}
}

return lDataSet;
}

/***************** END OF C# CODE **********************/

/***************** STORED PROCEDURE ******************/
CREATE PROCEDURE dbo.Usager_Liste
(
@id_LANGUE char(2),
@critere nvarchar(255),
@typeRecherche int,
@rechercherParmis int,
@classerPar int,
@triAscendant bit
)
AS

DECLARE @SELECT_STR nvarchar(1000)
DECLARE @FROM_STR nvarchar(1000)
DECLARE @WHERE_STR nvarchar(1000)
DECLARE @ORDERBY_STR nvarchar(1000)

SET @SELECT_STR = ' '
SET @FROM_STR = ' '
SET @ORDERBY_STR = ' '

SET @SELECT_STR = @SELECT_STR + 'SELECT TOP 100 '
SET @SELECT_STR = @SELECT_STR + 'U.id_USAGER, '
SET @SELECT_STR = @SELECT_STR + 'U.adresse_USAGER, '
SET @SELECT_STR = @SELECT_STR + 'U.id_LISTE_Province, '
SET @SELECT_STR = @SELECT_STR + 'LLProv.nom_LISTE_LANGUE AS
nomProvince, '
SET @SELECT_STR = @SELECT_STR + 'U.estImprime_USAGER '

SET @FROM_STR = @FROM_STR + 'FROM '
SET @FROM_STR = @FROM_STR + 'Usager AS U '
SET @FROM_STR = @FROM_STR + 'LEFT JOIN Liste_Langue LLProv ON
LLProv.id_LISTE = U.id_LISTE_Province '
SET @FROM_STR = @FROM_STR + 'AND LLProv.id_LANGUE = ''' + @id_LANGUE +
''' '

SET @ORDERBY_STR = 'ORDER BY '

/*********** Those IFs are bypassed ******************/
/*********** the 3 SET ORDERBY_STR statements are executed
**************/
IF @classerPar = 3
BEGIN
-- Email
SET @ORDERBY_STR = @ORDERBY_STR + 'U.email_USAGER, U.nom_USAGER,
U.prenom_USAGER '
END
ELSE
BEGIN
IF @classerPar = 2
BEGIN
-- Prenom
SET @ORDERBY_STR = @ORDERBY_STR + 'U.prenom_USAGER, U.nom_USAGER,
U.email_USAGER '
END
ELSE
BEGIN
-- Nom (1)
SET @ORDERBY_STR = @ORDERBY_STR + 'U.nom_USAGER, U.prenom_USAGER,
U.email_USAGER '
END
END


DECLARE @Tout nvarchar(4000)

SET @Tout = @SELECT_STR + @FROM_STR + @ORDERBY_STR

exec sp_ExecuteSQL @Tout

GO
/***************** END OF STORED PROCEDURE ******************/

 

[Kathleen Dollard]

Le Fletan,

Open up profiler, see what you're passing from .NET and copy that string
into Query Analyzer. and see what happens. Copy the string, don't retype
anything.

Did you cut and paste this stored procedure or didi you retype any of it?

Create a pseudo-tenp table and create variables immediately before and after
the If block. Write these variables to the pseudo temp table and see what
you've got.

I use conditionals in stored procedures and have not had a problem so I am
curious about what your seeing.

Kathleen

 

[Le Fletan]

Thank you Kathleen for your quick answer !

I did all the necessary checks to find out what was in my variables.
For example, I printed the generated SQL instead of executing it,
copied it and ran it in Query Analyzer (without any modifications) and
it is working...

Anyway, I figured that since the problem only occured when running the
stored procedure with a DataSet that the problem was around the way to
call it with a DataSet...So I finally got an idea and removed the call
to the FillSchema method of the DataAdapter. I figured that since it
was impossible for that method to produce a schema from a dynamic
stored it was useless. And you know what, everything works fine now !

If anyone knows the exact use of the the FillSchema method, please
post a short message. I think it's only usefull if you plan to update
the database from your DataSet and I never do that so I might as well
completly remove the call to that method...If I am wrong please let me
know !