Discussion :

[vhellers]

Bonjour,

J'ai une procédure stockée au sein de laquelle je veux utiliser du code sql que j'ai enregistré dans une table (dans un champ nvarchar).

Par exemple je voudrais que ma PS me retourne selon le cas le mois courrant.
_Dans ma table je stocke : month(getdate())
_Dans ma procédure je récupère dans une variable cette requête :
set @formule = (SELECT NUM_Formule FROM dbo.TNumerotation WHERE NUM_Libelle=@param0)
_Si je fais un : SELECT @formule > j'obtiens bien : month(getdate())
_si je fais : set @result = month(getdate()) > j'obtiens bien le mois courrant.

C'est donc comment remplacer ma formule (month(getdate()) dans set @result = month(getdate())) en dure par la variable @formule que je souhaite faire et où je bute.

set @result = @formule > me renvoie : month(getdate()) (et non le mois)
set @result = select (@formule) > me renvoie : erreur de syntaxe
set @result = exec (@formule) > me renvoie : erreur de syntaxe

D'avance merci pour votre aide.

[Madinico]

Le soucis est que tu souhaite stockée le résultat d'une requête dans une variable. Si tu avais plusieurs champs à récupérer, tu n'aurais pas penser à faire comme cela.

La solution que j'utilise est de stocker le résultat d'une requête dynamique dans une table temporaire. Et ensuite de manipuler la table.

[Clorish]

En fait la question est plus : Comment interpreter et executer une requete SQL dynamiquement, transmise par l'utilisateur au format Text (nvarchar)

[Edit]
J'ai fini par trouver :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
EXEC sp_executesql 'SELECT * FROM Table WHERE Champ = Valeur'

Pour plus d'infos, se referer a la doc M$

Référence de Transact-SQL


sp_executesql
Exécute une instruction ou un lot d'instructions Transact-SQL, réutilisable plusieurs fois ou créé dynamiquement. L'instruction ou le lot d'instructions Transact-SQL peut contenir des paramètres incorporés.

Syntaxe
sp_executesql [@stmt =] stmt
[
{, [@params =] N'@parameter_name data_type [,...n]' }
{, [@param1 =] 'value1' [,...n] }
]

Arguments
[@stmt =] stmt

Chaîne Unicode contenant une instruction ou un lot d'instructions Transact-SQL. L'argument stmt doit être une constante Unicode ou une variable pouvant implicitement être convertie en ntext. L'utilisation d'expressions Unicode plus complexes (comme par exemple la concaténation de deux chaînes avec l'opérateur +) n'est pas autorisée. L'utilisation de constantes de caractères n'est pas autorisée non plus. Si une constante est spécifiée, elle doit contenir le préfixe N. Par exemple, la constante Unicode N'sp_who' est autorisée, mais la constante de caractères 'sp_who' ne l'est pas. La taille de la chaîne n'est limitée que par la quantité de mémoire disponible sur le serveur de base de données.

stmt peut contenir des paramètres possédant la même forme qu'un nom de variable, comme par exemple :

N'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'

Chaque paramètre inclus dans stmt doit posséder une entrée correspondante dans la liste de définitions des paramètres @params et dans la liste des valeurs des paramètres.

[@params =] N'@parameter_name data_type [,...n]'

Chaîne contenant les définitions de tous les paramètres incorporés à stmt. Cette chaîne doit être une constante Unicode ou une variable pouvant implicitement être convertie en ntext. Chaque définition de paramètre se compose d'un nom de paramètre et d'un type de données. L'argument n est une marque de réservation pour d'autres définitions de paramètres. Chaque paramètre spécifié dans stmt doit être défini dans @params. Si l'instruction ou le lot d'instructions Transact-SQL contenu(e) dans stmt ne contient aucun paramètre, il est inutile d'utiliser @params. La valeur par défaut de ce paramètre est la valeur NULL.

[@param1 =] 'value1'

Valeur du premier paramètre défini dans la chaîne de paramètres. Cette valeur peut être une constante ou une variable. Chaque paramètre inclus dans stmt doit contenir une valeur de paramètre. Aucune valeur n'est requise si l'instruction ou le lot d'instructions Transact-SQL de stmt ne contient pas de paramètre.

n

Représente une marque de réservation destinée aux valeurs de paramètres supplémentaires. Ces valeurs doivent être des constantes ou des variables. Leur degré de complexité ne doit pas dépasser celui d'expressions telles que les fonctions ou expressions créées à l'aide d'opérateurs.

Valeurs des codes renvoyés
0 (succès) ou 1 (échec)

Jeux de résultats
Renvoie les jeux de résultats de toutes les instructions SQL de la chaîne SQL.

Notes
La procédure sp_executesql a le même comportement vis-à-vis des lots d'instructions, de l'étendue des noms et du contexte de base de données que l'instruction EXECUTE. L'instruction ou le lot d'instructions Transact-SQL du paramètre stmt de sp_executesql n'est compilé(e) qu'au moment de l'exécution de l'instruction sp_executesql. Le contenu de stmt est alors compilé et exécuté en tant que plan d'exécution distinct de celui du lot qui a appelé sp_executesql. Le lot sp_executesql ne peut pas faire référence à des variables déclarées dans le lot qui a appelé sp_executesql. Les curseurs locaux ou les variables du lot sp_executesql ne sont pas visibles pour le lot qui a appelé sp_executesql. Les modifications apportées au contexte de base de données ne durent que jusqu'à la fin de l'instruction sp_executesql.

La procédure sp_executesql peut être utilisée en remplacement des procédures stockées, afin d'exécuter une instruction Transact-SQL plusieurs fois lorsque la modification des valeurs de paramètres de l'instruction constitue l'unique changement. L'instruction Transact-SQL elle-même demeurant constante, seules les valeurs des paramètres étant modifiées, l'optimiseur de requête Microsoft® SQL Server™ peut réutiliser le plan d'exécution généré pour la première exécution.



Remarque Si la chaîne d'instruction contient des noms d'objet qui ne sont pas totalement qualifiés, le plan d'exécution n'est pas réutilisé.


La procédure sp_executesql prend en charge la définition des valeurs de paramètres en dehors de la chaîne Transact-SQL :

DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

/* Build the SQL string once.*/
SET @SQLString =
N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'
SET @ParmDefinition = N'@level tinyint'
/* Execute the string with the first parameter value. */
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@level = @IntVariable
/* Execute the same string with the second parameter value. */
SET @IntVariable = 32
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@level = @IntVariable

La possibilité de substitution de paramètres dans sp_executesql présente les avantages suivants lors de l'utilisation de l'instruction EXECUTE pour exécuter une chaîne :

Le texte réellement contenu dans l'instruction Transact-SQL de la chaîne sp_executesql ne changeant pas entre les différentes exécutions, l'optimiseur mettra probablement en correspondance l'instruction Transact-SQL de la deuxième exécution et le plan d'exécution généré pour la première exécution. Cela évite donc à SQL Server de devoir compiler la deuxième instruction.


La chaîne Transact-SQL est créée une seule fois.


Le paramètre de type entier est spécifié dans son format d'origine. La conversion en Unicode n'est pas nécessaire.
Autorisations
Les autorisations d'exécution reviennent par défaut au rôle public.

Exemple
A. Exécution d'une instruction SELECT simple
Cet exemple illustre la création et l'exécution d'une instruction SELECT simple contenant un paramètre incorporé nommé @level.

execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl = @level',
N'@level tinyint',
@level = 35

B. Exécution d'une chaîne créée dynamiquement
Cet exemple illustre l'utilisation de sp_executesql pour exécuter une chaîne créée dynamiquement. Cet exemple de procédure stockée est utilisé pour l'insertion de données dans un ensemble de tables servant à partitionner les données commerciales d'une année. Il existe une table par mois de l'année, au format suivant :

CREATE TABLE May1998Sales
(OrderID INT PRIMARY KEY,
CustomerID INT NOT NULL,
OrderDate DATETIME NULL
CHECK (DATEPART(yy, OrderDate) = 1998),
OrderMonth INT
CHECK (OrderMonth = 5),
DeliveryDate DATETIME NULL,
CHECK (DATEPART(mm, OrderDate) = OrderMonth)
)

Pour plus d'informations sur l'extraction de données de ces tables partitionnées, voir Utilisation des vues partitionnées.

Le nom de chaque table se compose des trois premières lettres du mois, des quatre chiffres de l'année et de la constante Sales. L'élaboration du nom peut s'effectuer dynamiquement à partir d'une date de commande :

/* Get the first three characters of the month name. */
SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
/* Concatenate the four-digit year; cast as character. */
CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) +
/* Concatenate the constant 'Sales'. */
'Sales'

Cet exemple de procédure stockée permet de créer et d'exécuter dynamiquement une instruction INSERT destinée à insérer les nouvelles commandes dans la table appropriée. La date de commande est utilisée pour créer le nom de la table devant contenir les données et l'incorporer ensuite à une instruction INSERT. (Il s'agit d'un exemple simple illustrant l'utilisation de sp_executesql. Il ne contient pas de détection d'erreur et n'inclut aucun contrôle des règles de l'entreprise, telles que la recherche de numéros de commandes en double dans les différentes tables.)

CREATE PROCEDURE InsertSales @PrmOrderID INT, @PrmCustomerID INT,
@PrmOrderDate DATETIME, @PrmDeliveryDate DATETIME
AS
DECLARE @InsertString NVARCHAR(500)
DECLARE @OrderMonth INT

-- Build the INSERT statement.
SET @InsertString = 'INSERT INTO ' +
/* Build the name of the table. */
SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) +
'Sales' +
/* Build a VALUES clause. */
' VALUES (@InsOrderID, @InsCustID, @InsOrdDate,' +
' @InsOrdMonth, @InsDelDate)'

/* Set the value to use for the order month because
functions are not allowed in the sp_executesql parameter
list. */
SET @OrderMonth = DATEPART(mm, @PrmOrderDate)

EXEC sp_executesql @InsertString,
N'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME,
@InsOrdMonth INT, @InsDelDate DATETIME',
@PrmOrderID, @PrmCustomerID, @PrmOrderDate,
@OrderMonth, @PrmDeliveryDate

GO

Pour cette procédure, l'utilisation de sp_executesql est plus efficace que l'utilisation de EXECUTE pour exécuter une chaîne. Si vous utilisez sp_executesql, seules 12 versions de la chaîne INSERT sont générées (une par table mensuelle). Avec EXECUTE, chaque chaîne INSERT est unique car les valeurs de paramètres diffèrent. Bien que ces deux méthodes génèrent le même nombre de lots d'instructions, la similitude des chaînes INSERT générées par sp_executesql renforce la probabilité de réutilisation des plans d'exécution par l'optimiseur de requête.



Voir aussi


Lots d'instructions

EXECUTE

Élaboration d'instructions lors de l'exécution

Procédures stockées du système

©1988-2000 Microsoft Corporation. Tous droits réservés.

[vhellers]

Comment interpreter et executer une requete SQL dynamiquement, transmise par l'utilisateur au format Text

oui c'est ce que j'écrivais plus haut :

utiliser du code sql que j'ai enregistré dans une table

mais ce qui compte c'est que la solution est là

@+

[Madinico]

Voici le morceau de code que j'utilise habituellement :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
-- Table temporaire locale
CREATE TABLE #TEMPO(
MaVar INT
)
 
DECLARE @QUERY VARCHAR(8000)
DECLARE @RES INT
 
SET @QUERY='SELECT unChampINT FROM MaTable WHERE ID=valeurUnique'
EXECUTE('INSERT INTO #TEMPO '+@QUERY)
SELECT @RES=MaVar FROM #TEMPO
PRINT @RES

Cette méthode est contraignante car on doit connaître le type et le nombre de champ résultant de la requête dynamique.

[Clorish]

Creer une table est un peu lourd pour une Procedure qui risuqe d'etre appelé assez regulierement.
Je craint aussi pour la gestion de la synchro ....