Comment passer une table temporaire en tant que paramètre dans une procédure stockée séparée
, j'ai une procédure stockée qui prend un paramètre d'entrée @CategoryKeys varchar et analyse de son contenu dans une table temporaire, #CategoryKeys.
-- create the needed temp table.
CREATE TABLE #CategoryKeys
(
CategoryKey SMALLINT
);
-- fill the temp table if necessary
IF Len(rtrim(ltrim(@CategoryKeys))) > 0
BEGIN
INSERT INTO #CategoryKeys
(CategoryKey)
SELECT value
FROM dbo.String_To_SmallInt_Table(@CategoryKeys, ',');
END
Si la table temporaire a des lignes, je voudrais passer la table dans une procédure stockée séparée. Comment vais-je créer un paramètre dans la procédure séparée pour contenir la table temporaire?
2 réponses
Lorsque vous créez une table #TEMP, la "portée" est plus grande que la procédure dans laquelle elle est créée.
Voici un échantillon:
IF EXISTS
(
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspProc002'
)
BEGIN
DROP PROCEDURE [dbo].[uspProc002]
END
GO
CREATE Procedure dbo.uspProc002
AS
BEGIN
/* Note, I did not Create #TableOne in this procedure. It "pre-existed". An if check will ensure that it is there. */
IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
begin
Insert into #TableOne ( SurrogateKey , NameOf ) select 2001, 'uspProc002'
end
END
GO
IF EXISTS
(
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspProc001'
)
BEGIN
DROP PROCEDURE [dbo].[uspProc001]
END
GO
CREATE Procedure dbo.uspProc001 (
@Param1 int
)
AS
BEGIN
IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
begin
drop table #TableOne
end
CREATE TABLE #TableOne
(
SurrogateKey int ,
NameOf varchar(12)
)
Insert into #TableOne ( SurrogateKey , NameOf ) select 1001, 'uspProc001'
Select * from #TableOne
EXEC dbo.uspProc002
Select * from #TableOne
IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
begin
drop table #TableOne
end
END
GO
exec dbo.uspProc001 0
CELA DIT, VEUILLEZ NE PAS CODER BEAUCOUP D'ENTRE EUX. C'EST L'ÉQUIVALENT SQL D'UNE VARIABLE GLOBALE ET IL EST DIFFICILE À MAINTENIR ET SUJET AUX BOGUES.
Bien que la compréhension de la portée réponde au besoin direct, il pourrait être utile d'ajouter quelques options supplémentaires au mélange pour développer les suggestions des commentaires.
- passer XML dans la procédure stockée
- passe un paramètre de valeur de table dans la procédure stockée
1. Passez XML dans la procédure stockée
Avec XML passé dans un paramètre, vous pouvez utiliser le XML directement dans vos requêtes SQL et joindre / appliquer à d'autres tableaux:
CREATE PROC sp_PassXml
@Xml XML
AS
BEGIN
SET NOCOUNT ON
SELECT T.Node.value('.', 'int') AS [Key]
FROM @Xml.nodes('/keys/key') T (Node)
END
GO
Puis un appel à la procédure stockée pour tester:
DECLARE @Text XML = '<keys><key>1</key><key>2</key></keys>'
EXEC sp_PassXml @Text
Exemple de sortie d'une requête simple.
Key
-----------
1
2
2. Passez un paramètre de valeur de table dans la procédure stockée
Tout d'abord, vous devez définir le type défini par l'utilisateur pour la variable de table à utiliser par la procédure stockée.
CREATE TYPE KeyTable AS TABLE ([Key] INT)
Ensuite, vous pouvez utiliser ce type comme paramètre pour le proc stocké (le READONLY est requis car seul IN est pris en charge et la table ne peut pas être Modifié)
CREATE PROC sp_PassTable
@Keys KeyTable READONLY
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM @Keys
END
GO
Le proc stocké peut alors être appelé avec une variable de table directement à partir de SQL.
DECLARE @Keys KeyTable
INSERT @Keys VALUES (1), (2)
EXEC sp_PassTable @Keys
Remarque: Si vous utilisez. Net, vous pouvez passer le paramètre SQL à partir d'un type DataTable correspondant au type défini par l'utilisateur.
Exemple de sortie de la requête:
Key
-----------
1
2