Comment faire un EXEC dans une fonction

Version imprimable

Bonjour,

Je cherche à créer une fonction basé sur un curseur lui même basé sur une requête variable. La création du curseur fonctionne trés bien par contre quand je veux l'intégrer dans une fonction j'ai le message d'erreur me disant qu'il n'est pas possible EXEC dans une fonction

Code:

1
2
3
 
Msg 443, Level 16, State 14, Procedure supp_modele_table, Line 14
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
CREATE FUNCTION [dbo].[supp_modele_table](@database varchar(20), @formatTable varchar(20))
RETURNS int 
AS 
BEGIN 
	DECLARE @nb_table int
	DECLARE @sql varchar(200)
 
	SET @nb_table=0
	SET @sql='SELECT name FROM ' + @database + '.[dbo].[sysobjects] WHERE xtype in (''U'',''V'') and name like ''' + @formatTable + ''''
 
	DECLARE @nom_table varchar(255)
	DECLARE @dropCommand varchar(255)
 
	EXEC('DECLARE tableCursor CURSOR FOR ' + @sql)
 
	OPEN tableCursor
		FETCH next FROM tableCursor INTO @nom_table
 
		IF @@fetch_status<>0 
			BEGIN 
				SET @nb_table=0 
			END
			ELSE
			BEGIN
				WHILE @@fetch_status=0
				BEGIN
					SET @dropcommand = N'drop table ' + @database + '.[dbo].' + @nom_table
					SET @nb_table= @nb_table +1
					EXEC(@dropcommand)
					FETCH next FROM tableCursor INTO @nom_table
				END
			END
	CLOSE tableCursor
 
	DEALLOCATE tableCursor
 
    RETURN @nb_table;
END;

Est ce qu'il existe une autre méthode que EXEC ?

D'avance merci

27/05/2014, 17h31
mikedavem

Dans ton cas une procédure stockée est plus adaptée ...

Attention également au type d'objet U et V. Si tu veux supprimer une vue il faudra utiliser DROP VIEW et non DROP TABLE

++
27/05/2014, 17h56
baime

Effectivement ça va beaucoup mieux.

Du coup je vais poser une question bête dans quels cas il vaut mieux mieux utiliser une fonction plutôt qu'une procédure stockée et inversement ?
27/05/2014, 19h52
mikedavem

Les procédures stockées et les fonctions vont tous les 2 te permettrent d'exécuter un ensemble de commandes T-SQL voir même te retourner un résultat.

En revanche les fonctions sont plus adaptés lorsqu'elles doivent être utilisés :
- dans des clauses de requêtes par exemple WHERE , HAVING
- pour retourner des résultats dans une projection de requête (prend en entrée une valeur et doit retourner une valeur)
- lorsque le résultat doit être utilisé comme un ensemble de lignes devant être réutilisés dans des jointures

Les procédures stockées sont plus utilisés pour exécuter un ensemble de commandes pour répondre à une demande utilisateur (de manière basique créer un utilisateur, récupérer l'identifiant d'une commande ... etc ou comme dans ton cas supprimer un ensemble d'objets correspondants à un format dans une base de données et retourner le nombre d'objets concernés). Tu pourras donc effectuer d'autres opérations qu'un simple SELECT contrairement à une fonction ...

Bon je ne traite pas ici les aspects performances à prendre en compte quant à l'utilisation des UDF ou TPV ...

Par ailleurs il faut que tu saches que tu n'as pas besoin d'utiliser un curseur pour supprimer un ensemble d'objets dans une base de données et retourner leur nombre.

++
28/05/2014, 08h33
baime

Merci pour les infos c'est plus clair.

Tu dis que je n'étais pas obligé d'utiliser un curseur, mais dans ce cas comment j'aurais pu faire ? 8O

Je suis preneur de toutes lidées c'est comme ça que l'on progresse ;)

++

J'aurais vu quelque chose comme cela (à adapter dans une procédure stockée et ton contexte)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
 
SET NOCOUNT ON;
DECLARE @DATABASE SYSNAME = 'compare';
DECLARE @formatTable VARCHAR(20) = '%t%';
DECLARE @nb_table_deleted INT;
DECLARE @sql NVARCHAR(MAX);
DECLARE @error_message NVARCHAR(4000);
 
 
SET @sql= N'SELECT object_id, name, type 
		    FROM ' + QUOTENAME(@DATABASE) + '.[sys].[objects] 
		    WHERE type in (''U'',''V'') 
		     AND name like ''' + @formatTable + '''';
 
DECLARE @T_DROP TABLE
(
	[object_id] INT,
	nom_table SYSNAME,
	type_table CHAR(1)
);
 
INSERT @T_DROP
EXEC (@sql);
 
SET @sql = N'';
 
SELECT 
	@sql = @sql + 
	N'DROP ' +
	CASE type_table
		WHEN 'U' THEN N' TABLE '
		ELSE N' VIEW '
	END + nom_table + ';'
FROM @T_DROP
 
BEGIN TRY
	EXEC sp_executesql @sql;
 
	SELECT	
		@nb_table_deleted = COUNT(*)
	FROM @T_DROP
END TRY
BEGIN CATCH
	 SET @error_message = N'An error occured during the drop table operation with' + CHAR(13)  
						   + ' error number : ' + CAST(ERROR_NUMBER() AS VARCHAR(5))  
						   + ' ,severity : ' + CAST(ERROR_SEVERITY() AS varchar(5)) 
						   + ' ,state : ' + CAST(ERROR_STATE() AS varchar(5)) + CHAR(13) 
						   + ' message : ' + ERROR_MESSAGE()
 
 
	PRINT @error_message;
END CATCH
 
PRINT '';
PRINT 'nb objects deleted ' + CAST(COALESCE(@nb_table_deleted, 0) AS varchar(10));

28/05/2014, 10h04
baime

Alors là bravo :ave:

ça parait évident une fois qu'on le voit écrit. J'ai encore du chemin à faire

Encore merci :ccool:
28/05/2014, 10h18
SQLpro
Une UDF (User Defined Function), c'est à dire une fonction définie par l'utilisateur (en complément des fonctions standard du SQL) est destinée à être utilisée principalement au sein d'une requête. Elle doit donc être transactionnellement cohérente. Elle ne peut donc pas contenir :
- d'ordre de mise à jour INSERT, UPDATE, SELECT ou MERGE
- piloter explicitement la transaction
- utiliser du SQL dynamique
- utiliser un curseur
Par conséquence elle ne peut pas non plus appeler une procédure.
En effet on verrait mal en effectuant un simple SELECT qui utilise une fonction, voir la transaction "muter" en mise à jour !

Les procédures stockées, sont par contre faites pour cela. Ceci faisant partie intégrante de la norme SQL, sous ensemble PSM Persistent Stored Module).

Je note en outre que vous utilisez des tables systèmes dont sysobjects qui sont obsolète depuis la version 2005 de SQL Server et donc capable de vous donner des informations erronées.

A +
28/05/2014, 10h23
baime

Merci SQLPro.

Je suis en version SQL Server 2012 et ça me retourne pourtant bien la liste des mes tables, vues ou synonymes. Si sysobjects n'est pas à utiliser laquelle est préconisé ?

++
28/05/2014, 13h59
mikedavem

Celle que j'ai mise dans le script ^^ : sys.objects

++
28/05/2014, 14h04
baime

C'est pas faux :lol:

J'ai lu un peu vite [sys].[objects] ce n'est pas [sysobjects] :oops:

Je crois que le week-end s'impose

++