procedure acceptant un nombre variable de paramètres

Version imprimable

28/03/2007, 15h16
pifou25

procedure acceptant un nombre variable de paramètres

Bonjour,

peut-on déclarer une proc stock qui accepte comme arguments un nombre variables de paramètres? C'est simple, selon le choix de l'utilisateur la proc fera un filtre sur un ou plusieurs ID (WHERE Id IN ( ... ) ) il faudrait donc lui transmettre un tableau, de manière à pouvoir écrire: WHERE Id IN @tableau

las, déjà le type tableau n'existe pas en transac sql...!

Tu peux passer un comma delimited parameter.

Code:

@p_ID_LIST = '10, 12, 35, 202'

Ensuite tu crées du sql dynamique dans ta procédure. Voici un exemple du sql dynamique.

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
CREATE PROCEDURE [dbo].[USP_UBI_PRODUCT_GET_TRANSACTIONS]
-- ****************************************************************************
-- PARAM
-- ****************************************************************************
@p_STATUS		varchar(50)		= NULL, 
@p_SOURCE		varchar(50)		= NULL,
@p_OWNER		nvarchar(255)	= NULL,
@p_VALIDATOR	nvarchar(255)	= NULL
 
AS
-- ****************************************************************************
-- DECLARE
-- ****************************************************************************
DECLARE @SQL nvarchar(4000),
		@PARAMS nvarchar(500)
-- ****************************************************************************
-- GLOBAL INIT
-- ****************************************************************************
SET NOCOUNT ON
-- ****************************************************************************
-- PROCESS
-- ****************************************************************************
 
SET @SQL = 'SELECT [TRANSACTION_ID] ,[REF_TABLE] ,[LOCAL_KEY] ,[STATUS] ,[SOURCE] ,[TRANSACTION_TYPE] '
SET @SQL = @SQL + ' ,[NOTES],[OWNER],[VALIDATOR],[DATE_CREATED],[DATE_MODIFIED] '
SET @SQL = @SQL + ' FROM [UBI_PRODUCTS].[dbo].[UBI_PRODUCT_TRANSACTION] '
SET @SQL = @SQL + ' WHERE 1 = 1 '
 
IF @p_STATUS IS NOT NULL
	SET @SQL = @SQL + ' AND STATUS = @p_STATUS '
 
IF @p_SOURCE IS NOT NULL
	SET @SQL = @SQL + ' AND SOURCE = @p_SOURCE '
 
IF @p_OWNER IS NOT NULL
	SET @SQL = @SQL + ' AND OWNER = @p_OWNER '
 
IF @p_VALIDATOR IS NOT NULL
	SET @SQL = @SQL + ' AND VALIDATOR = @p_VALIDATOR '
 
SET @PARAMS = '@p_STATUS varchar(50), @p_SOURCE varchar(50), @p_OWNER nvarchar(255), @p_VALIDATOR nvarchar(255) '
 
EXEC sp_executesql @SQL, @PARAMS, @p_STATUS = @p_STATUS,  @p_SOURCE = @p_SOURCE, @p_OWNER = @p_OWNER, @p_VALIDATOR = @p_VALIDATOR
 
 
-- ****************************************************************************
-- RETURN
-- ****************************************************************************
 
GO

Tu n'auras qu'à faire un clause

Code:

@SQL = @SQL + 'AND Id IN (' + @p_ID_LIST + ') '

voici une autre possibilité qu'on m'a proposé, sur le meme principe: une variable contient toutes les valeurs concaténées; une fonction SPLIT qui renvoie une table à une colonne:

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
CREATE FUNCTION [dbo].Split(
   @PInStrSource varchar(8000) = NULL, @pInChrSeparator char(1) = ';'
 )
 RETURNS @ARRAY TABLE (ItemValue VARCHAR(1000))
AS
BEGIN
 
DECLARE @CurrentStr varchar(2000)
DECLARE @ItemStr varchar(200)
 
SET @CurrentStr = @PInStrSource
 
WHILE Datalength(@CurrentStr) > 0
BEGIN
	IF CHARINDEX(@pInChrSeparator, @CurrentStr,1) > 0 
	BEGIN
		SET @ItemStr = SUBSTRING (@CurrentStr, 1, CHARINDEX(@pInChrSeparator, @CurrentStr,1) - 1)
		SET @CurrentStr = SUBSTRING (@CurrentStr, CHARINDEX(@pInChrSeparator,
			@CurrentStr,1) + 1, (Datalength(@CurrentStr) - CHARINDEX(@pInChrSeparator, @CurrentStr,1) + 1))
		INSERT @ARRAY (ItemValue) VALUES (@ItemStr)
	END
	ELSE
    BEGIN                
		INSERT @ARRAY (ItemValue) VALUES (@CurrentStr)    
		BREAK;
	END 
END
RETURN
 
END

puis dans ma fonction qui reçoit l'argument à traiter @IdSelProgr :

Code:

1
2
CREATE TABLE #tmp_SelProg (SelectedProg VARCHAR(50))
INSERT INTO #tmp_SelProg SELECT ItemValue FROM dbo.Split( @IdSelProgr, ';')