Comment coder une requete dynamique avec une Variable binaire.

Version imprimable

03/03/2009, 09h10
BoromSikim

Comment coder une requete dynamique avec une Variable binaire.

Bonjour à tous.

Je dois tester une variable binaire dans une requete dynamique, et je n'arrive pas à trouver de solution.

Un exemple:
/* Soit une table ERP, disposant d'un champ appelé Colonne_Timestamp de type Time Stamp.
Je dois l'historiser dans une table Datamart au format presque identique.
Dans cette table datamart le champ Colonne_Timestamp de type BINARY(8).
*/

@company --> contient un code société passé en parametre de la ProcSto.

Declare @Maximum as binary(8)
Declace @Requete as varchar(max)

-- Je détermine le TimeStamp maximum de la table datamart.
Set @Maximum = (select max(colonne_timestamp) from table_datamart

-- Je récupère dans ERP tous les Time Stamp supérieurs à @Maximum
Set @Requete = 'insert into table_datamart select C1, C2, C3, ... '
+ ' from ' + ''' + @company + ''' + '.table_erp '
+ 'where table_erp.colonne_timestamp > ' + @maximum

Mon probleme se situe au niveau de la clause where!
le 'where table_erp.colonne_timestamp > ' + @maximum ne passe pas.

Auriez vous une idée, ou mieux une solution ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
/* Soit une table ERP, disposant d'un champ appelé Colonne_Timestamp de type Time Stamp.
Je dois l'historiser dans une table Datamart au format presque identique.
Dans cette table datamart le champ Colonne_Timestamp de type BINARY(8).
*/
 
@company --> contient un code société passé en parametre de la ProcSto.
 
Declare @Maximum as binary(8)
Declace @Requete as varchar(max)
 
-- Je détermine le TimeStamp maximum de la table datamart.
Set @Maximum = (select max(colonne_timestamp) from table_datamart
 
-- Je récupère dans ERP tous les Time Stamp supérieurs à @Maximum
Set @Requete = 'insert into table_datamart select C1, C2, C3, ... '
+ ' from ' + ''' + @company + ''' + '.table_erp '
+ 'where table_erp.colonne_timestamp > ' + @maximum

:merci:

et pourquoi tu ferais pas une sous requète? au lieu d'avoir une requète qui te remplis ta variable @maximum, pourquoi ne pas faire

Code:

1
2
 
+ 'where table_erp.colonne_timestamp > (select max(colonne_timestamp) from table_datamart)'

PS : en haut tu as 2 declare. change les en

Code:

1
2
3
 
Declare @Maximum as binary(8)
      , @Requete as varchar(max)

03/03/2009, 09h52
BoromSikim

Je l 'ai fait bien sur, mais ma proc sto dynamique codée avec un
(select max(colonne_timestamp) from table_datamart)'
va drolement plus lentement que quand je travaille avec une requête statique codée comme indiqué.
J'interprete cette lenteur au fait que la sous requete est évaluée à chaque ligne non ?
03/03/2009, 10h12
elsuket
Bonjour,

Avez-vous essayé en encadrant votre valeur binaire par des quotes ?
Code:

1 2 3 SET @Requete = 'insert into table_datamart select C1, C2, C3, ... ' + ' from ' + ''' + @company + ''' + '.table_erp ' + 'where table_erp.colonne_timestamp > ''' + @maximum + ''
Dans tous les cas pour savoir ce que vous passez comme requête, utilisez la commande PRINT @Requete avant de l'exécuter ;)

@++ ;)
03/03/2009, 11h03
BoromSikim

J avais essayé bien sur...
Message
The data types varchar and binary are incompatible in the add operator.

Exact, j'y ai repensé 5 minutes après en me disant qu'avec sp_executeSQL cela doit fonctionner :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE @SQL NVARCHAR(256),
		@ParamDef NVARCHAR(255),
		@bin BINARY(8)
 
SELECT @SQL = 'INSERT INTO table_datamart ' +
			' SELECT C1, C2, C3, ...' +
			' FROM ' + @company + '.table_erp' +
			' WHERE colonne_timestamp > @bin',
		@ParamDef = N'@binIN BINARY(8)',
		@bin = 0x0000000000000005
 
EXEC master.dbo.sp_executeSQL @SQL, @ParamDef, @binIN = @bin

Dites-nous si c'est OK ;)

@++ ;)

Bonjour,

Et utilisant la fonction master.dbo.fn_varbintohexstr ? (Voir code ci dessous)
Edit => En plus de celle proposée par Elsuket

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/* Soit une table ERP, disposant d'un champ appelé Colonne_Timestamp de type Time Stamp.
Je dois l'historiser dans une table Datamart au format presque identique.
Dans cette table datamart le champ Colonne_Timestamp de type BINARY(8).
*/
 
@company --> contient un code société passé en parametre de la ProcSto.
 
Declare @Maximum AS BINARY(8)
Declace @Requete AS varchar(max)
 
-- Je détermine le TimeStamp maximum de la table datamart.
SELECT @Maximum = max(colonne_timestamp) FROM table_datamart
 
-- Je récupère dans ERP tous les Time Stamp supérieurs à @Maximum
SET @Requete = 'insert into table_datamart select C1, C2, C3, ... '
+ ' from ' + @company + '.table_erp '
+ 'where table_erp.colonne_timestamp > ' + master.dbo.fn_varbintohexstr(@maximum)
 
EXEC(@Requete)

03/03/2009, 15h23
BoromSikim

Merci à elsuket et mikedavem.

Solution de ELSUKET OK.

Declare @Requete as nvarchar(max)
Declare @maxim as binary(8)
Declare @ParamDef as NVARCHAR(500)

set @maxim = (select max([timestamp]) from [mserveur].[mbase].[dbo].[msociete$G_L Entry])
Set @requete = 'select * from [mserveur].[mbase].[dbo].[msociete$G_L Entry] '
+ 'where [Timestamp] = @maximum '
Set @ParamDef = N'@maximum BINARY(8)'

EXECUTE sp_executeSQL @requete, @ParamDef, @maximum = @maxim

Solution de mikedavem OK

Declare @Requete as varchar(max)
Declare @maxim as binary(8)

set @maxim = (select max([timestamp]) from [mserveur].[mbase].[dbo].[msociete$G_L Entry])

Set @requete = 'select * from [mserveur].[mbase].[dbo].[msociete$G_L Entry] '
+ 'where [Timestamp] = ' + master.dbo.fn_varbintohexstr(@maxim)
Execute (@requete)
03/03/2009, 16h28
elsuket

Ceci me conforte dans l'idée de bannir l'utilisation de EXEC(@SQL) pour exécuter du code SQL dynamique :

- le plan des requêtes exécutées avec sp_executeSQL est conservé, ce qui n'est pas le cas avec EXEC(@SQL)
- le contexte d'exécution est le même que la procédure appelante lorsqu'on utilise sp_executeSQL, mais pas avec EXEC(@SQL),
- on peut retourner des valeurs avec sp_executeSQL, ce qui n'est pas possible avec EXEC(@SQL)

@++ ;)
03/03/2009, 16h57
mikedavem

En terme de performance il est kler qu'il vaut mieux utiliser sp_executsql pour les raisons que tu as cité.

L'important dans la 2ème solution est simplement de voir qu'il est possible d'utiliser la fonction master.dbo.fn_varbintohexstr()

++ ;)
03/03/2009, 17h16
elsuket

Une fonction intéressante, mais qui n'est pas documentée ...
A utiliser à nos risques et périls donc :aie:

@++ ;)
03/03/2009, 17h17
mikedavem

Aussi ;)