Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11

Passage de table ou Array en parametre à une UDF !!

4 réponses
Avatar
BossHog
Bonjour,
voila ma question:
je fait appel à cette PS affin quelle me retourne les nom des champs de la
table passée en param de (@table_name )
en excluant ce spécifiés en param de (@COL).
l'exclusion ce fait par un not in en bas de l'udf.
le probléme est que je n'arrive pas à lui passé plusieure valeur en
parametre exemple, ja voudrai passé le nom des champs à exclure du resultat
por obtenir l'équivalent de ceci dans le not in ('col1','col2','col3') en
sachant que le not in est en fait ecrit not in (@COL).
j'ai essayer diverse syntaxe avec doublage et triplage de cotes et rien ne
marche, j'ai dailleur cherché à passé un tableau en param mais je n'est pas
trouvé comment faire alors si vous pouviez m'aider je vous en serai
reconnaissant.

à l'heure actuelle je m'oriente vers une solution utilisant OPENXML mais je
trouve sa un peu lourd pour les utilisateur de l'udf alors si y'a plus
simple je suis preneur
Merci

PS: Je précise qund mème que je débute en SQL
--*********************************************
--Cette UDF est dérivée de la PS system sp_sproc_columns
--*********************************************
ALTER FUNCTION GET_COLUMNS_TABLE_NAMES_WITHOUT_SPECIFIED (
@table_name nvarchar(384),
@COL SQL_VARIANT
)
RETURNS @TABLE TABLE(COLUMN_NAME text)
AS
BEGIN
DECLARE @full_table_name nvarchar(769)
DECLARE @table_id int
DECLARE @ODBCVer int
Set @ODBCVer = 2

if @ODBCVer <> 3
Begin
select @ODBCVer = 2
End

SELECT @table_id = object_id(@full_table_name)

INSERT @TABLE
SELECT
COLUMN_NAME = convert(sysname,c.name)
FROM
sysobjects o,
master.dbo.spt_datatype_info d,
systypes t,
syscolumns c
LEFT OUTER JOIN syscomments m on c.cdefault = m.id
AND m.colid = 1
WHERE
o.name like @table_name
AND o.id = c.id
AND t.xtype = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and
c.number = 0))
AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name,
'IsIdentity'),0)
AND c.xusertype = t.xusertype
AND c.name not in (@COL)
RETURN
END

4 réponses

Avatar
Fred BROUARD
Part de cette base :

DECLARE @TABLE_NAME VARCHAR(128)
SET @TABLE_NAME = 'T_ACTEUR_ATR'

DECLARE @COLUMNS VARCHAR(8000)
SET @COLUMNS = ''

DECLARE @SQL VARCHAR(8000)

SELECT @COLUMNS = @COLUMNS+', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME

SELECT SUBSTRING(@COLUMNS, 3, LEN(@COLUMNS) -2)

A +

BossHog a écrit:
Bonjour,
voila ma question:
je fait appel à cette PS affin quelle me retourne les nom des champs de la
table passée en param de (@table_name )
en excluant ce spécifiés en param de (@COL).
l'exclusion ce fait par un not in en bas de l'udf.
le probléme est que je n'arrive pas à lui passé plusieure valeur en
parametre exemple, ja voudrai passé le nom des champs à exclure du resultat
por obtenir l'équivalent de ceci dans le not in ('col1','col2','col3') en
sachant que le not in est en fait ecrit not in (@COL).
j'ai essayer diverse syntaxe avec doublage et triplage de cotes et rien ne
marche, j'ai dailleur cherché à passé un tableau en param mais je n'est pas
trouvé comment faire alors si vous pouviez m'aider je vous en serai
reconnaissant.

à l'heure actuelle je m'oriente vers une solution utilisant OPENXML mais je
trouve sa un peu lourd pour les utilisateur de l'udf alors si y'a plus
simple je suis preneur
Merci

PS: Je précise qund mème que je débute en SQL
--*********************************************
--Cette UDF est dérivée de la PS system sp_sproc_columns
--*********************************************
ALTER FUNCTION GET_COLUMNS_TABLE_NAMES_WITHOUT_SPECIFIED (
@table_name nvarchar(384),
@COL SQL_VARIANT
)
RETURNS @TABLE TABLE(COLUMN_NAME text)
AS
BEGIN
DECLARE @full_table_name nvarchar(769)
DECLARE @table_id int
DECLARE @ODBCVer int
Set @ODBCVer = 2

if @ODBCVer <> 3
Begin
select @ODBCVer = 2
End

SELECT @table_id = object_id(@full_table_name)

INSERT @TABLE
SELECT
COLUMN_NAME = convert(sysname,c.name)
FROM
sysobjects o,
master.dbo.spt_datatype_info d,
systypes t,
syscolumns c
LEFT OUTER JOIN syscomments m on c.cdefault = m.id
AND m.colid = 1
WHERE
o.name like @table_name
AND o.id = c.id
AND t.xtype = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and
c.number = 0))
AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name,
'IsIdentity'),0)
AND c.xusertype = t.xusertype
AND c.name not in (@COL)
RETURN
END






--
Frédéric BROUARD - expert SQL, spécialiste : SQL Server / Delphi / web
Livre SQL - col. Référence : http://sqlpro.developpez.com/bookSQL.html" target="_blank" class="text-blue hover:opacity-90 " style="word-break: break-all;" rel="noopener nofollow">http://sqlpro.developpez.com/bookSQL.html
Le site du SQL, pour débutants et pros : http://sqlpro.developpez.com
****************** mailto: ******************
Avatar
BossHog
Merci Merci Merci,
c'est excellent je me suis meme permis d'ajouter ceci dans la clause where
"and COLUMN_NAME not in ('SUPPLIERGROUPCODE_ID','SUPPLIERGROUPCODE')"
ce qui fait que je vais pouvoir effacer mon usine à gaz, faite à base de
tables temp et de curseur.

pourrais tu me dire ou trouver plus d'infos sur INFORMATION_SCHEMA car
j'aurai d'autres besoins comme par exemple: checker le nombre de param d'une
PS Etc...
et je ne trouve rien quand je cherche dans le book SQL online
Encore Merci à toi
BossHog
jf pédrazzani
MobileWorkers


"Fred BROUARD" wrote in message
news:
Part de cette base :

DECLARE @TABLE_NAME VARCHAR(128)
SET @TABLE_NAME = 'T_ACTEUR_ATR'

DECLARE @COLUMNS VARCHAR(8000)
SET @COLUMNS = ''

DECLARE @SQL VARCHAR(8000)

SELECT @COLUMNS = @COLUMNS+', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME

SELECT SUBSTRING(@COLUMNS, 3, LEN(@COLUMNS) -2)

A +

BossHog a écrit:
> Bonjour,
> voila ma question:
> je fait appel à cette PS affin quelle me retourne les nom des champs de


la
> table passée en param de (@table_name )
> en excluant ce spécifiés en param de (@COL).
> l'exclusion ce fait par un not in en bas de l'udf.
> le probléme est que je n'arrive pas à lui passé plusieure valeur en
> parametre exemple, ja voudrai passé le nom des champs à exclure du


resultat
> por obtenir l'équivalent de ceci dans le not in ('col1','col2','col3')


en
> sachant que le not in est en fait ecrit not in (@COL).
> j'ai essayer diverse syntaxe avec doublage et triplage de cotes et rien


ne
> marche, j'ai dailleur cherché à passé un tableau en param mais je n'est


pas
> trouvé comment faire alors si vous pouviez m'aider je vous en serai
> reconnaissant.
>
> à l'heure actuelle je m'oriente vers une solution utilisant OPENXML mais


je
> trouve sa un peu lourd pour les utilisateur de l'udf alors si y'a plus
> simple je suis preneur
> Merci
>
> PS: Je précise qund mème que je débute en SQL
> --*********************************************
> --Cette UDF est dérivée de la PS system sp_sproc_columns
> --*********************************************
> ALTER FUNCTION GET_COLUMNS_TABLE_NAMES_WITHOUT_SPECIFIED (
> @table_name nvarchar(384),
> @COL SQL_VARIANT
> )
> RETURNS @TABLE TABLE(COLUMN_NAME text)
> AS
> BEGIN
> DECLARE @full_table_name nvarchar(769)
> DECLARE @table_id int
> DECLARE @ODBCVer int
> Set @ODBCVer = 2
>
> if @ODBCVer <> 3
> Begin
> select @ODBCVer = 2
> End
>
> SELECT @table_id = object_id(@full_table_name)
>
> INSERT @TABLE
> SELECT
> COLUMN_NAME = convert(sysname,c.name)
> FROM
> sysobjects o,
> master.dbo.spt_datatype_info d,
> systypes t,
> syscolumns c
> LEFT OUTER JOIN syscomments m on c.cdefault = m.id
> AND m.colid = 1
> WHERE
> o.name like @table_name
> AND o.id = c.id
> AND t.xtype = d.ss_dtype
> AND c.length = isnull(d.fixlen, c.length)
> AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
> AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF')


and
> c.number = 0))
> AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name,
> 'IsIdentity'),0)
> AND c.xusertype = t.xusertype
> AND c.name not in (@COL)
> RETURN
> END
>
>
>

--
Frédéric BROUARD - expert SQL, spécialiste : SQL Server / Delphi / web
Livre SQL - col. Référence : http://sqlpro.developpez.com/bookSQL.html" target="_blank" class="text-blue hover:opacity-90 " style="word-break: break-all;" rel="noopener nofollow">http://sqlpro.developpez.com/bookSQL.html
Le site du SQL, pour débutants et pros : http://sqlpro.developpez.com
****************** mailto: ******************



Avatar
Dominique Peralta
Pour une fois que Fred répond sans faire référence à un capitre de son site,
voilà qu'on lui réclame.
Alors je vais le faire à sa place ;-))

http://sqlpro.developpez.com/SQL_AZ_7b.html" target="_blank" class="text-blue hover:opacity-90 " style="word-break: break-all;" rel="noopener nofollow">http://sqlpro.developpez.com/SQL_AZ_7b.html
Chapitre 9



"BossHog" a écrit dans le message de
news:%
Merci Merci Merci,
c'est excellent je me suis meme permis d'ajouter ceci dans la clause where
"and COLUMN_NAME not in ('SUPPLIERGROUPCODE_ID','SUPPLIERGROUPCODE')"
ce qui fait que je vais pouvoir effacer mon usine à gaz, faite à base de
tables temp et de curseur.

pourrais tu me dire ou trouver plus d'infos sur INFORMATION_SCHEMA car
j'aurai d'autres besoins comme par exemple: checker le nombre de param


d'une
PS Etc...
et je ne trouve rien quand je cherche dans le book SQL online
Encore Merci à toi
BossHog
jf pédrazzani
MobileWorkers


"Fred BROUARD" wrote in message
news:
> Part de cette base :
>
> DECLARE @TABLE_NAME VARCHAR(128)
> SET @TABLE_NAME = 'T_ACTEUR_ATR'
>
> DECLARE @COLUMNS VARCHAR(8000)
> SET @COLUMNS = ''
>
> DECLARE @SQL VARCHAR(8000)
>
> SELECT @COLUMNS = @COLUMNS+', '+COLUMN_NAME
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = @TABLE_NAME
>
> SELECT SUBSTRING(@COLUMNS, 3, LEN(@COLUMNS) -2)
>
> A +
>
> BossHog a écrit:
> > Bonjour,
> > voila ma question:
> > je fait appel à cette PS affin quelle me retourne les nom des champs


de
la
> > table passée en param de (@table_name )
> > en excluant ce spécifiés en param de (@COL).
> > l'exclusion ce fait par un not in en bas de l'udf.
> > le probléme est que je n'arrive pas à lui passé plusieure valeur en
> > parametre exemple, ja voudrai passé le nom des champs à exclure du
resultat
> > por obtenir l'équivalent de ceci dans le not in ('col1','col2','col3')
en
> > sachant que le not in est en fait ecrit not in (@COL).
> > j'ai essayer diverse syntaxe avec doublage et triplage de cotes et


rien
ne
> > marche, j'ai dailleur cherché à passé un tableau en param mais je


n'est
pas
> > trouvé comment faire alors si vous pouviez m'aider je vous en serai
> > reconnaissant.
> >
> > à l'heure actuelle je m'oriente vers une solution utilisant OPENXML


mais
je
> > trouve sa un peu lourd pour les utilisateur de l'udf alors si y'a plus
> > simple je suis preneur
> > Merci
> >
> > PS: Je précise qund mème que je débute en SQL
> > --*********************************************
> > --Cette UDF est dérivée de la PS system sp_sproc_columns
> > --*********************************************
> > ALTER FUNCTION GET_COLUMNS_TABLE_NAMES_WITHOUT_SPECIFIED (
> > @table_name nvarchar(384),
> > @COL SQL_VARIANT
> > )
> > RETURNS @TABLE TABLE(COLUMN_NAME text)
> > AS
> > BEGIN
> > DECLARE @full_table_name nvarchar(769)
> > DECLARE @table_id int
> > DECLARE @ODBCVer int
> > Set @ODBCVer = 2
> >
> > if @ODBCVer <> 3
> > Begin
> > select @ODBCVer = 2
> > End
> >
> > SELECT @table_id = object_id(@full_table_name)
> >
> > INSERT @TABLE
> > SELECT
> > COLUMN_NAME = convert(sysname,c.name)
> > FROM
> > sysobjects o,
> > master.dbo.spt_datatype_info d,
> > systypes t,
> > syscolumns c
> > LEFT OUTER JOIN syscomments m on c.cdefault = m.id
> > AND m.colid = 1
> > WHERE
> > o.name like @table_name
> > AND o.id = c.id
> > AND t.xtype = d.ss_dtype
> > AND c.length = isnull(d.fixlen, c.length)
> > AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
> > AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF',


'IF')
and
> > c.number = 0))
> > AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id,


c.name,
> > 'IsIdentity'),0)
> > AND c.xusertype = t.xusertype
> > AND c.name not in (@COL)
> > RETURN
> > END
> >
> >
> >
>
> --
> Frédéric BROUARD - expert SQL, spécialiste : SQL Server / Delphi / web
> Livre SQL - col. Référence : http://sqlpro.developpez.com/bookSQL.html" target="_blank" class="text-blue hover:opacity-90 " style="word-break: break-all;" rel="noopener nofollow">http://sqlpro.developpez.com/bookSQL.html
> Le site du SQL, pour débutants et pros : http://sqlpro.developpez.com
> ****************** mailto: ******************
>




Avatar
BossHog
Merci
pour info la méthode qui m'a indique Fred est elle appliquable à une table
temporaire ??
j'ai essayer et sa retourne rien du tout
pour palier à ce probleme j'utilise le code suivant(**CODE:**) pour créer
une table pseudo-temporaire que je drop à la fin.
ma question est:
1-peut on utiliser INFORMATION_SCHEMA sur des tables temporaires
2-D'après vous ma méthode de création de table pseudo-temporaire me garantie
elle contre le risque de doublons lors de la creation
Merci d'avance


**CODE:**
--Defini le nom unique de la table (temporaire) qui sera crée
DECLARE @TABLEIDNAME uniqueidentifier
DECLARE @TABLE_UNIQUE_NAME varchar(500)
--Génération d'un nom unique pour la table pseudo-temporaire
Set @TABLEIDNAME = NEWID()
Set @TABLE_UNIQUE_NAME = Replace(convert(varchar(255),@TABLEIDNAME),'-','')

plus loin je la crée dynamiquement comme ceci
set @SQLSTRING = @SQLSTRING + ' into ['+@TABLE_UNIQUE_NAME+'] from ' +
@REFERENTIAL_TYPE



"Dominique Peralta" wrote in message
news:
Pour une fois que Fred répond sans faire référence à un capitre de son


site,
voilà qu'on lui réclame.
Alors je vais le faire à sa place ;-))

http://sqlpro.developpez.com/SQL_AZ_7b.html" target="_blank" class="text-blue hover:opacity-90 " style="word-break: break-all;" rel="noopener nofollow">http://sqlpro.developpez.com/SQL_AZ_7b.html
Chapitre 9



"BossHog" a écrit dans le message de
news:%
> Merci Merci Merci,
> c'est excellent je me suis meme permis d'ajouter ceci dans la clause


where
> "and COLUMN_NAME not in ('SUPPLIERGROUPCODE_ID','SUPPLIERGROUPCODE')"
> ce qui fait que je vais pouvoir effacer mon usine à gaz, faite à base de
> tables temp et de curseur.
>
> pourrais tu me dire ou trouver plus d'infos sur INFORMATION_SCHEMA car
> j'aurai d'autres besoins comme par exemple: checker le nombre de param
d'une
> PS Etc...
> et je ne trouve rien quand je cherche dans le book SQL online
> Encore Merci à toi
> BossHog
> jf pédrazzani
> MobileWorkers
>
>
> "Fred BROUARD" wrote in message
> news:
> > Part de cette base :
> >
> > DECLARE @TABLE_NAME VARCHAR(128)
> > SET @TABLE_NAME = 'T_ACTEUR_ATR'
> >
> > DECLARE @COLUMNS VARCHAR(8000)
> > SET @COLUMNS = ''
> >
> > DECLARE @SQL VARCHAR(8000)
> >
> > SELECT @COLUMNS = @COLUMNS+', '+COLUMN_NAME
> > FROM INFORMATION_SCHEMA.COLUMNS
> > WHERE TABLE_NAME = @TABLE_NAME
> >
> > SELECT SUBSTRING(@COLUMNS, 3, LEN(@COLUMNS) -2)
> >
> > A +
> >
> > BossHog a écrit:
> > > Bonjour,
> > > voila ma question:
> > > je fait appel à cette PS affin quelle me retourne les nom des champs
de
> la
> > > table passée en param de (@table_name )
> > > en excluant ce spécifiés en param de (@COL).
> > > l'exclusion ce fait par un not in en bas de l'udf.
> > > le probléme est que je n'arrive pas à lui passé plusieure valeur en
> > > parametre exemple, ja voudrai passé le nom des champs à exclure du
> resultat
> > > por obtenir l'équivalent de ceci dans le not in


('col1','col2','col3')
> en
> > > sachant que le not in est en fait ecrit not in (@COL).
> > > j'ai essayer diverse syntaxe avec doublage et triplage de cotes et
rien
> ne
> > > marche, j'ai dailleur cherché à passé un tableau en param mais je
n'est
> pas
> > > trouvé comment faire alors si vous pouviez m'aider je vous en serai
> > > reconnaissant.
> > >
> > > à l'heure actuelle je m'oriente vers une solution utilisant OPENXML
mais
> je
> > > trouve sa un peu lourd pour les utilisateur de l'udf alors si y'a


plus
> > > simple je suis preneur
> > > Merci
> > >
> > > PS: Je précise qund mème que je débute en SQL
> > > --*********************************************
> > > --Cette UDF est dérivée de la PS system sp_sproc_columns
> > > --*********************************************
> > > ALTER FUNCTION GET_COLUMNS_TABLE_NAMES_WITHOUT_SPECIFIED (
> > > @table_name nvarchar(384),
> > > @COL SQL_VARIANT
> > > )
> > > RETURNS @TABLE TABLE(COLUMN_NAME text)
> > > AS
> > > BEGIN
> > > DECLARE @full_table_name nvarchar(769)
> > > DECLARE @table_id int
> > > DECLARE @ODBCVer int
> > > Set @ODBCVer = 2
> > >
> > > if @ODBCVer <> 3
> > > Begin
> > > select @ODBCVer = 2
> > > End
> > >
> > > SELECT @table_id = object_id(@full_table_name)
> > >
> > > INSERT @TABLE
> > > SELECT
> > > COLUMN_NAME = convert(sysname,c.name)
> > > FROM
> > > sysobjects o,
> > > master.dbo.spt_datatype_info d,
> > > systypes t,
> > > syscolumns c
> > > LEFT OUTER JOIN syscomments m on c.cdefault = m.id
> > > AND m.colid = 1
> > > WHERE
> > > o.name like @table_name
> > > AND o.id = c.id
> > > AND t.xtype = d.ss_dtype
> > > AND c.length = isnull(d.fixlen, c.length)
> > > AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
> > > AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF',
'IF')
> and
> > > c.number = 0))
> > > AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id,
c.name,
> > > 'IsIdentity'),0)
> > > AND c.xusertype = t.xusertype
> > > AND c.name not in (@COL)
> > > RETURN
> > > END
> > >
> > >
> > >
> >
> > --
> > Frédéric BROUARD - expert SQL, spécialiste : SQL Server / Delphi / web
> > Livre SQL - col. Référence : http://sqlpro.developpez.com/bookSQL.html" target="_blank" class="text-blue hover:opacity-90 " style="word-break: break-all;" rel="noopener nofollow">http://sqlpro.developpez.com/bookSQL.html
> > Le site du SQL, pour débutants et pros : http://sqlpro.developpez.com
> > ****************** mailto: ******************
> >
>
>