Procédure stockée : la variable scalaire doit être déclarée

Version imprimable

Bonjour, j'ai écris une procédure stockée pour remplir une grille en fonction du profil de l'utilisateur connecté. Le problème c'est que ça me renvoie une erreur, et je ne sais quoi faire.Aidez moi s'il vous plait. Voilà ma PS:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
ALTER PROCEDURE [dbo].[PS_REMPLIR_GRILLE_PRODEMANDE]
				 @Profil int
AS
BEGIN
 
	DECLARE @Script varchar(8000)
	SET @Script = 'SELECT DISTINCT DEMANDE_INTERVENTION.IdDemande_PK , DEMANDE_INTERVENTION.Objet , 
						DEMANDE_INTERVENTION.DteDemande , DEMANDE_INTERVENTION.DteDebut  , 
						DEMANDE_INTERVENTION.DteFin, DEMANDE_INTERVENTION.NomDemandeur, DEMANDE_INTERVENTION.PrenomDemandeur, 
						DEMANDE_INTERVENTION.IdClient_FK,DEMANDE_INTERVENTION.IdStatut_FK, DEMANDE_INTERVENTION.Observations  
					FROM  DEMANDE_INTERVENTION inner join STATUT inner join PROFIL_STATUT
					ON STATUT.IdStatut_PK = PROFIL_STATUT.IdStatut_FK
					ON DEMANDE_INTERVENTION.IdStatut_FK = PROFIL_STATUT.IdStatut_FK
					WHERE PROFIL_STATUT.IdProfil_FK = + @Profil'
	SET @Script = @Script
	EXEC(@Script)
END

Et voilà le résultat que j'obtiens :

Citation:

Msg 137, Niveau 15, État 2, Ligne 8
La variable scalaire "@Profil" doit être déclarée.

(1 ligne(s) affectée(s))

Il faut ou bien ajouter la chaine directement - et donc ne pas laisser @Profil dans la chaine, mais la valeur utilisée - ce qui n'est pas conseillé si la requête est souvent exécutée :

Code:

1
2
3
4
5
6
7
8
9
 
	SET @Script = 'SELECT DISTINCT DEMANDE_INTERVENTION.IdDemande_PK , DEMANDE_INTERVENTION.Objet , 
						DEMANDE_INTERVENTION.DteDemande , DEMANDE_INTERVENTION.DteDebut  , 
						DEMANDE_INTERVENTION.DteFin, DEMANDE_INTERVENTION.NomDemandeur, DEMANDE_INTERVENTION.PrenomDemandeur, 
						DEMANDE_INTERVENTION.IdClient_FK,DEMANDE_INTERVENTION.IdStatut_FK, DEMANDE_INTERVENTION.Observations  
					FROM  DEMANDE_INTERVENTION inner join STATUT inner join PROFIL_STATUT
					ON STATUT.IdStatut_PK = PROFIL_STATUT.IdStatut_FK
					ON DEMANDE_INTERVENTION.IdStatut_FK = PROFIL_STATUT.IdStatut_FK
					WHERE PROFIL_STATUT.IdProfil_FK = '+ CONVERT(VARCHAR,@Profil)

Ou bien déclarer la variable lors de l'exécution.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
 
DECLARE @ParmDefinition nvarchar(500)
DECLARE @Script varchar(8000)
 
SET @ParmDefinition = N'@ProfilUsed int'
 
 
	SET @Script = 'SELECT DISTINCT DEMANDE_INTERVENTION.IdDemande_PK , DEMANDE_INTERVENTION.Objet , 
						DEMANDE_INTERVENTION.DteDemande , DEMANDE_INTERVENTION.DteDebut  , 
						DEMANDE_INTERVENTION.DteFin, DEMANDE_INTERVENTION.NomDemandeur, DEMANDE_INTERVENTION.PrenomDemandeur, 
						DEMANDE_INTERVENTION.IdClient_FK,DEMANDE_INTERVENTION.IdStatut_FK, DEMANDE_INTERVENTION.Observations  
					FROM  DEMANDE_INTERVENTION inner join STATUT inner join PROFIL_STATUT
					ON STATUT.IdStatut_PK = PROFIL_STATUT.IdStatut_FK
					ON DEMANDE_INTERVENTION.IdStatut_FK = PROFIL_STATUT.IdStatut_FK
					WHERE PROFIL_STATUT.IdProfil_FK = @ProfilUsed'
 
EXECUTE sp_executesql @Script , @ParmDefinition,
                      @ProfilUsed = @Profil;

20/08/2012, 15h20
zinzineti
Citation:
Envoyé par Rei Ichido

Code:

1 2 WHERE PROFIL_STATUT.IdProfil_FK = '+ CONVERT(VARCHAR,@Profil)
/!\ Attention, lorsque la longueur du VARCHAR n'est pas spécifiée dans le CONVERT (ou CAST) la longueur par défaut est 30. Donc tenir compte de la longueur maximale de @Profile.
Je te propose dans ta procédure de :
0. d'abord tester si le profile en entrée est un entier (integer); si oui alors
1. construire ta requête puis exécuter
Il faut aussi prendre l'habitude d'imposer l'option

Code:

SET NOCOUNT ON

au début des procédures stockées.
20/08/2012, 16h53
Kropernic

Citation:

Envoyé par zinzineti

Il faut aussi prendre l'habitude d'imposer l'option

Code:

SET NOCOUNT ON

au début des procédures stockées.

Cela apporte... ? (un gain en performance ?)
20/08/2012, 17h15
zinzineti

Citation:

Envoyé par Kropernic

Cela apporte... ? (un gain en performance ?)

L'option

Code:

SET NOCOUNT ON

évite le renvoi du message indiquant le nombre de lignes retournées par la requête. Donc diminue le niveau de bavardage côté SGBD. On économise ainsi des ressources et donc gain de perf
20/08/2012, 22h40
Kropernic

Sur la théorie, 100% d'accord. Mais en pratique, est-ce vraiment significatif ? Dans mon environnement de travail, je pense que je ne verrai jamais la différence alors je vous demande...
20/08/2012, 22h55
zinzineti

Si ça peut te convaincre :-)
21/08/2012, 08h59
Kropernic
Pas moyen de lire l'article (à part dans le code source mais bon...)

~~Je ne reçois pas leur e-mail de confirmation :(~~

Je viens de le recevoir (9:29) alors que je me suis inscrit hier soir XD.

Je vais donc m'instruire et merci pour le lien ! (et pour le site que je ne connaissais pas)

EDIT : Je me permets de copier une partie de l'article linké par zinzinneti pour ceux qui comme moi début dans l'administration car cela me semble important.
Citation:
Points to consider

The performance boost is due to the few bytes of information that make up the "(1 row(s) affected)" message not being transmitted to the client application.
With this in mind I should consider the following points

Communication between the database and the client application on a stand-alone machine will be as fast as it is possible to get. If your front end application had it's own clock and you recorded the time from submitting the query to the time when the client finally received the full results I would expect that transmitting results across a network to be slower.
In this experiment we are carrying out a single simple insert statement. If your procedure carries out multiple operations the performance boost will be more pronounced.
For queries that retrieve data the performance boost will be less simply because the size of the "(1 row(s) affected)" message is small compared to the volume of data being returned.
In .NET applications an ExecuteNonQuery command returns the number of records affected by the operation. Set NOCOUNT ON means that the value that this call returns is always zero.
En gras, le passage qui me semble le plus important (sûrement dû au fait que je suis programmeur).