L
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 ******************/
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
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
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 ******************/