Création de fonction comptant le nb de lignes avec paramètres

**frearnaud** · 10/12/2015, 11h32

Bonjour à tous !

Je cherche à créer une fonction me permettant de compter le nombre de lignes dans une table dont le nom est déterminé par l'un des paramètres.
Le problème c'est que mon script de création ne passe pas vraiment

J'obtiens l'erreur suivante : SQL Server Database Error: La variable de table "@sTableName" doit être déclarée.

Pouvez vous m'éclairer sur ce qu'il se passe ici ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
create FUNCTION [dbo].[fn_getNbPts]
(@IdCpt int, @TypePt int, @dtDebut datetime, @dtFin datetime)
RETURNS int
WITH EXEC AS CALLER
AS
BEGIN
DECLARE @sTableName nvarchar(50) = 'crb' + right('00000' + convert(nvarchar(5), lIdCpt), 5);
DECLARE @iCount int;
 
select @iCount = count(id_conso)
          from @sTableName c
          where c.type_pt_id = convert(varchar(10),@TypePt)
          and c.version = (select max(c2.version) 
                          from @sTableName c2
                          where c2.datepoint = c.datepoint
                          and c2.type_pt_id = c.type_pt_id
                          and c2.compteur_id = c.compteur_id)
          and c.datepoint between @dtDebut and @dtFin
 
return @iCount;
END
GO

**escartefigue** · 10/12/2015, 11h35

Vous devez en ce cas utiliser du SQL Dynamique : préparation de la requete à partir de vos variables et des constantes, puis demande d'exécution de la requête construite

**SQLpro** · 10/12/2015, 15h19

Envoyé par escartefigue

Vous devez en ce cas utiliser du SQL Dynamique : préparation de la requête à partir de vos variables et des constantes, puis demande d'exécution de la requête construite

Et en complément, il n'est pas possible de faire du SQL dynamique dans des fonctions, car le but d'une fonction est de faire des lectures ce que le SQL dynamique ne peut limiter par nature.

Orientez vous vers les procédures.

A +

Invité · 10/12/2015, 17h48

J'utilise le code ci-dessous pour faire ça.

Code :

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

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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
-- Donne le nombre d'enregistrements par tables
CREATE TABLE #TableCount ( table_schema varchar(250), table_name varchar(250), card int,
	ReservedSize_ko int, DataSize_ko int, IndexSize_ko int, UnusedSize_ko int
	PRIMARY KEY ( table_schema , table_name ) )
 
CREATE TABLE #spaceused_tab
	( tableName varchar(100), numberofRows varchar(100), reservedSize varchar(50), 
		dataSize varchar(50), indexSize varchar(50), unusedSize varchar(50) )
 
declare @sql2 nvarchar(1500)
declare @table_schema varchar(255)
declare @table_name varchar(255)
 
DECLARE db_cursor CURSOR FOR 
	select 
		REQ2 = 'INSERT  #spaceused_tab EXEC sp_spaceused ''' + QUOTENAME(table_schema) + '.' + QUOTENAME(table_name) + ''''
		, table_schema
		, table_name
	from information_schema.tables 
	where table_type = 'BASE TABLE'
		--and TABLE_NAME = 'archive_fix_charges'
	FOR READ ONLY
 
OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @sql2, @table_schema, @table_name
 
WHILE @@FETCH_STATUS = 0 
BEGIN 
	TRUNCATE table #spaceused_tab	
	exec sp_executesql @sql2
 
	insert #TableCount
	select @table_schema, @table_name , 
		numberofRows, 
		reverse( substring( reverse( reservedSize ) , 4 , 999)),
		reverse( substring( reverse( dataSize ) , 4 , 999)),
		reverse( substring( reverse( indexSize ) , 4 , 999)),
		reverse( substring( reverse( unusedSize ) , 4 , 999))
	from #spaceused_tab
 
	FETCH NEXT FROM db_cursor INTO @sql2, @table_schema, @table_name
END 
CLOSE db_cursor 
DEALLOCATE db_cursor
 
; WITH SR_col as (
	select COL.TABLE_CATALOG, COL.TABLE_SCHEMA, COL.TABLE_NAME, COUNT(*) nb_colonnes
	from INFORMATION_SCHEMA.COLUMNS COL with (nolock)
	group by COL.TABLE_CATALOG, COL.TABLE_SCHEMA, COL.TABLE_NAME
) , SR_Ind AS (
	select OBJECT_NAME(IND.object_id) name
		, OBJECT_SCHEMA_NAME(IND.object_id) schema_name
		, IND.object_id, COUNT(*) nb_indexes
	from SYS.indexes IND with (nolock)
	where IND.index_id > 0
	group by IND.object_id
)
SELECT TAB.TABLE_CATALOG, TAB.TABLE_SCHEMA, TAB.TABLE_NAME
	, max(SR_col.nb_colonnes) nb_colonnes
	, MAX(case when SR_ind.nb_indexes is null then 0 else SR_ind.nb_indexes end) nb_indexes
	, sum(case when CNT.CONSTRAINT_TYPE = 'PRIMARY KEY' then 1 else 0 end) PK
	, sum(case when CNT.CONSTRAINT_TYPE = 'UNIQUE' then 1 else 0 end) UK
	, sum(case when CNT.CONSTRAINT_TYPE = 'FOREIGN KEY' then 1 else 0 end) FK
	, sum(case when CNT.CONSTRAINT_TYPE = 'CHECK' then 1 else 0 end) CHK
	, max(TBC.card) nb_lignes
	, cast(max(TBC.ReservedSize_ko)/1024. as decimal(12,3)) ReservedSize_Mo
	, cast(max(TBC.DataSize_ko)/1024. as decimal(12,3)) DataSize_Mo
	, cast(max(TBC.IndexSize_ko)/1024. as decimal(12,3)) IndexSize_Mo
	, cast(max(TBC.UnusedSize_ko)/1024. as decimal(12,3)) UnusedSize_Mo
FROM information_schema.tables TAB with (nolock)
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS CNT with (nolock)
	on CNT.CONSTRAINT_CATALOG = TAB.TABLE_CATALOG
		and CNT.CONSTRAINT_SCHEMA = TAB.TABLE_SCHEMA
		and CNT.TABLE_NAME = TAB.TABLE_NAME
LEFT OUTER JOIN SR_col with (nolock)
	on SR_col.TABLE_CATALOG = TAB.TABLE_CATALOG
		and SR_col.TABLE_SCHEMA = TAB.TABLE_SCHEMA
		and SR_col.TABLE_NAME = TAB.TABLE_NAME
LEFT OUTER JOIN SR_Ind with (nolock)
	ON SR_Ind.schema_name = TAB.TABLE_SCHEMA
		and SR_Ind.name = TAB.TABLE_NAME
left outer join #TableCount TBC  with (nolock)--order by card desc , table_schema , table_name
	on TBC.table_schema = TAB.TABLE_SCHEMA COLLATE database_default
		and TBC.table_name = TAB.TABLE_NAME COLLATE database_default
WHERE TAB.table_type='BASE TABLE'
	and TAB.TABLE_NAME not in ( 'dtproperties' , 'sysdiagrams' )
group by TAB.TABLE_CATALOG, TAB.TABLE_SCHEMA, TAB.TABLE_NAME
order by ReservedSize_Mo desc, TAB.TABLE_CATALOG, TAB.TABLE_SCHEMA, TAB.TABLE_NAME
 
drop TABLE #TableCount 
drop TABLE #spaceused_tab
 
GO

**elsuket** · 14/12/2015, 14h09

Bonjour,

On peut utiliser pour ce faire la DMV sys.dm_db_partition_stats, ce qui évite donc l'appel à la procédure stockée sp_spaceused et donc le curseur

@++

**frearnaud** · 15/12/2015, 08h18

Merci pour vos réponses, je suis passé par une procédure et cela fonctionne parfaitement !

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
BEGIN
  DECLARE @sTableName varchar(50) = 'crb' + right('00000'+convert(varchar(5), @IdCpt), 5);
  DECLARE @sql nvarchar(MAX);
  DECLARE @iCount int;
  DECLARE @params nvarchar(MAX);
 
  SET @sql = N'select @iCountOUT = count(conso_id)
               from '+@sTableName+' c
               where c.type_pt_id = convert(varchar(10),'+convert(varchar(3),@TypePt)+')
               and c.version = (select max(c2.version) 
                                from '+@sTableName+' c2
                                where c2.datepoint = c.datepoint
                                and c2.type_pt_id = c.type_pt_id
                                and c2.compteur_id = c.compteur_id)
               and c.datepoint between ''' + @dtDebut + ''' and ''' + @dtFin + ''''
 
  SET @params = N'@iCountOUT int OUTPUT';
 
  exec sp_executesql @sql, @params, @iCountOUT = @iCount OUTPUT;
 
  SET @iOutput = @iCount;
 
  END

Invité · 15/12/2015, 17h59

Envoyé par elsuket

On peut utiliser pour ce faire la DMV sys.dm_db_partition_stats, ce qui évite donc l'appel à la procédure stockée sp_spaceused et donc le curseur

Merci du tuyau, je vais regarder ça à tête reposée. J'avoue que la perf de cette requête n'est pas critique, puisque j'applique ça sur des bds qui ne sont pas encore en prod ou sur une version preprod. Mais comme toujours, je suis d'accord, c'est toujours mieux d'avoir le meilleur...

Création de fonction comptant le nb de lignes avec paramètres [2008R2]

Développement SQL Server

Discussions similaires

Partager

Partager