Mixing
How to pass a temp table as a parameter into a separate stored procedure
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a stored procedure that takes an input parameter @CategoryKeys varchar, and parses its contents into a temp table, #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
If the temp table has rows, I would like to pass the table into a separate stored procedure. How would I go about creating a parameter in the separate procedure to hold the temp table?
sql
sql-server stored-procedures edited Nov 20 '13 at 19:46
marc_s
585k13011261272
asked Nov 20 '13 at 19:43
bsivelbsivel
1,77931624
add a comment |
I have a stored procedure that takes an input parameter @CategoryKeys varchar, and parses its contents into a temp table, #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
If the temp table has rows, I would like to pass the table into a separate stored procedure. How would I go about creating a parameter in the separate procedure to hold the temp table?
sql
sql-server stored-procedures edited Nov 20 '13 at 19:46
marc_s
585k13011261272
asked Nov 20 '13 at 19:43
bsivelbsivel
1,77931624
Instead of passing a comma-separated list in the first place, have you thought about using table-valued parameters? Also have you read this article?
– Aaron Bertrand
Nov 20 '13 at 19:47
Or XML, as SQL Server offers support for XML data type...
– Mihai Bejenariu
Nov 20 '13 at 19:53
add a comment |
I have a stored procedure that takes an input parameter @CategoryKeys varchar, and parses its contents into a temp table, #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
If the temp table has rows, I would like to pass the table into a separate stored procedure. How would I go about creating a parameter in the separate procedure to hold the temp table?
sql
sql-server stored-procedures edited Nov 20 '13 at 19:46
marc_s
585k13011261272
asked Nov 20 '13 at 19:43
bsivelbsivel
1,77931624
I have a stored procedure that takes an input parameter @CategoryKeys varchar, and parses its contents into a temp table, #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
If the temp table has rows, I would like to pass the table into a separate stored procedure. How would I go about creating a parameter in the separate procedure to hold the temp table?
sql
sql-server stored-procedures sql
sql-server stored-procedures edited Nov 20 '13 at 19:46
marc_s
585k13011261272
asked Nov 20 '13 at 19:43
bsivelbsivel
1,77931624
edited Nov 20 '13 at 19:46
marc_s
585k13011261272
asked Nov 20 '13 at 19:43
bsivelbsivel
1,77931624
edited Nov 20 '13 at 19:46
marc_s
585k13011261272
edited Nov 20 '13 at 19:46
marc_s
585k13011261272
edited Nov 20 '13 at 19:46
marc_s
585k13011261272
585k13011261272
asked Nov 20 '13 at 19:43
bsivelbsivel
1,77931624
asked Nov 20 '13 at 19:43
bsivelbsivel
1,77931624
asked Nov 20 '13 at 19:43
bsivelbsivel
1,77931624
1,77931624
Instead of passing a comma-separated list in the first place, have you thought about using table-valued parameters? Also have you read this article?
– Aaron Bertrand
Nov 20 '13 at 19:47
Or XML, as SQL Server offers support for XML data type...
– Mihai Bejenariu
Nov 20 '13 at 19:53
add a comment |
Instead of passing a comma-separated list in the first place, have you thought about using table-valued parameters? Also have you read this article?
– Aaron Bertrand
Nov 20 '13 at 19:47
Or XML, as SQL Server offers support for XML data type...
– Mihai Bejenariu
Nov 20 '13 at 19:53
Instead of passing a comma-separated list in the first place, have you thought about using table-valued parameters? Also have you read this article?
– Aaron Bertrand
Nov 20 '13 at 19:47
Instead of passing a comma-separated list in the first place, have you thought about using table-valued parameters? Also have you read this article?
– Aaron Bertrand
Nov 20 '13 at 19:47
Or XML, as SQL Server offers support for XML data type...
– Mihai Bejenariu
Nov 20 '13 at 19:53
Or XML, as SQL Server offers support for XML data type...
– Mihai Bejenariu
Nov 20 '13 at 19:53
add a comment |
2 Answers
2
active
oldest
votes
When you create a #TEMP table, the "scope" is bigger than just the procedure it is created in.
Below is a sample:
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
/* Uncomment this code if you want to be more explicit about bad "wiring" */
/*
IF OBJECT_ID('tempdb..#TableOne') IS NULL
begin
THROW 51000, 'The procedure expects a temp table named #TableOne to already exist.', 1;
end
*/
/* 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
HAVING SAID THAT, PLEASE DO NOT CODE UP ALOT OF THESE. ITS THE SQL EQUIVALENT OF A GLOBAL VARIABLE AND IT IS DIFFICULT TO MAINTAIN AND BUG PRONE.
answered Nov 20 '13 at 20:01
granadaCodergranadaCoder
15.3k55581
add a comment |
While understanding scoping addresses the direct need, thought it might be useful to add a few more options to the mix to elaborate on the suggestions from the comments.
- Pass XML into the stored procedure
- Pass a table-valued parameter into the stored procedure
1. Pass XML into the stored procedure
With XML passed into a parameter, you can use the XML directly in your SQL queries and join/apply to other tables:
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
Then a call to the stored procedure for testing:
DECLARE @Text XML = '<keys><key>1</key><key>2</key></keys>'
EXEC sp_PassXml @Text
Sample output of a simple query.
Key
-----------
1
2
2. Pass a table-valued parameter into the stored procedure
First, you have to define the user defined type for the table variable to be used by the stored procedure.
CREATE TYPE KeyTable AS TABLE ([Key] INT)
Then, you can use that type as a parameter for the stored proc (the READONLY is required since only IN is supported and the table cannot be changed)
CREATE PROC sp_PassTable
@Keys KeyTable READONLY
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM @Keys
END
GO
The stored proc can then be called with a table variable directly from SQL.
DECLARE @Keys KeyTable
INSERT @Keys VALUES (1), (2)
EXEC sp_PassTable @Keys
Note: If you are using .NET, then you can pass the SQL parameter from a DataTable type matching the user defined type.
Sample output from the query:
Key
-----------
1
2
answered Nov 28 '15 at 5:09
Jason WJason W
10.3k21644
2
While I have the accepted answer to the question, I am upvoting this because "just because the scope works".....doesn't mean its a good solution. Treating #tempTables like a global variable is bad routine practice. And I do exactly this, I pass xml all the time to promote set based operations to avoid cursors and RBAR.
– granadaCoder
Feb 15 '18 at 15:24
add a comment |
protected by Community♦ Jan 9 '16 at 20:15
Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
When you create a #TEMP table, the "scope" is bigger than just the procedure it is created in.
Below is a sample:
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
/* Uncomment this code if you want to be more explicit about bad "wiring" */
/*
IF OBJECT_ID('tempdb..#TableOne') IS NULL
begin
THROW 51000, 'The procedure expects a temp table named #TableOne to already exist.', 1;
end
*/
/* 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
HAVING SAID THAT, PLEASE DO NOT CODE UP ALOT OF THESE. ITS THE SQL EQUIVALENT OF A GLOBAL VARIABLE AND IT IS DIFFICULT TO MAINTAIN AND BUG PRONE.
answered Nov 20 '13 at 20:01
granadaCodergranadaCoder
15.3k55581
add a comment |
When you create a #TEMP table, the "scope" is bigger than just the procedure it is created in.
Below is a sample:
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
/* Uncomment this code if you want to be more explicit about bad "wiring" */
/*
IF OBJECT_ID('tempdb..#TableOne') IS NULL
begin
THROW 51000, 'The procedure expects a temp table named #TableOne to already exist.', 1;
end
*/
/* 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
HAVING SAID THAT, PLEASE DO NOT CODE UP ALOT OF THESE. ITS THE SQL EQUIVALENT OF A GLOBAL VARIABLE AND IT IS DIFFICULT TO MAINTAIN AND BUG PRONE.
answered Nov 20 '13 at 20:01
granadaCodergranadaCoder
15.3k55581
add a comment |
When you create a #TEMP table, the "scope" is bigger than just the procedure it is created in.
Below is a sample:
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
/* Uncomment this code if you want to be more explicit about bad "wiring" */
/*
IF OBJECT_ID('tempdb..#TableOne') IS NULL
begin
THROW 51000, 'The procedure expects a temp table named #TableOne to already exist.', 1;
end
*/
/* 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
HAVING SAID THAT, PLEASE DO NOT CODE UP ALOT OF THESE. ITS THE SQL EQUIVALENT OF A GLOBAL VARIABLE AND IT IS DIFFICULT TO MAINTAIN AND BUG PRONE.
answered Nov 20 '13 at 20:01
granadaCodergranadaCoder
15.3k55581
When you create a #TEMP table, the "scope" is bigger than just the procedure it is created in.
Below is a sample:
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
/* Uncomment this code if you want to be more explicit about bad "wiring" */
/*
IF OBJECT_ID('tempdb..#TableOne') IS NULL
begin
THROW 51000, 'The procedure expects a temp table named #TableOne to already exist.', 1;
end
*/
/* 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
HAVING SAID THAT, PLEASE DO NOT CODE UP ALOT OF THESE. ITS THE SQL EQUIVALENT OF A GLOBAL VARIABLE AND IT IS DIFFICULT TO MAINTAIN AND BUG PRONE.
answered Nov 20 '13 at 20:01
granadaCodergranadaCoder
15.3k55581
edited Jan 3 at 15:03
answered Nov 20 '13 at 20:01
granadaCodergranadaCoder
15.3k55581
answered Nov 20 '13 at 20:01
granadaCodergranadaCoder
15.3k55581
answered Nov 20 '13 at 20:01
granadaCodergranadaCoder
15.3k55581
15.3k55581
add a comment |
add a comment |
While understanding scoping addresses the direct need, thought it might be useful to add a few more options to the mix to elaborate on the suggestions from the comments.
- Pass XML into the stored procedure
- Pass a table-valued parameter into the stored procedure
1. Pass XML into the stored procedure
With XML passed into a parameter, you can use the XML directly in your SQL queries and join/apply to other tables:
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
Then a call to the stored procedure for testing:
DECLARE @Text XML = '<keys><key>1</key><key>2</key></keys>'
EXEC sp_PassXml @Text
Sample output of a simple query.
Key
-----------
1
2
2. Pass a table-valued parameter into the stored procedure
First, you have to define the user defined type for the table variable to be used by the stored procedure.
CREATE TYPE KeyTable AS TABLE ([Key] INT)
Then, you can use that type as a parameter for the stored proc (the READONLY is required since only IN is supported and the table cannot be changed)
CREATE PROC sp_PassTable
@Keys KeyTable READONLY
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM @Keys
END
GO
The stored proc can then be called with a table variable directly from SQL.
DECLARE @Keys KeyTable
INSERT @Keys VALUES (1), (2)
EXEC sp_PassTable @Keys
Note: If you are using .NET, then you can pass the SQL parameter from a DataTable type matching the user defined type.
Sample output from the query:
Key
-----------
1
2
answered Nov 28 '15 at 5:09
Jason WJason W
10.3k21644
2
While I have the accepted answer to the question, I am upvoting this because "just because the scope works".....doesn't mean its a good solution. Treating #tempTables like a global variable is bad routine practice. And I do exactly this, I pass xml all the time to promote set based operations to avoid cursors and RBAR.
– granadaCoder
Feb 15 '18 at 15:24
add a comment |
While understanding scoping addresses the direct need, thought it might be useful to add a few more options to the mix to elaborate on the suggestions from the comments.
- Pass XML into the stored procedure
- Pass a table-valued parameter into the stored procedure
1. Pass XML into the stored procedure
With XML passed into a parameter, you can use the XML directly in your SQL queries and join/apply to other tables:
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
Then a call to the stored procedure for testing:
DECLARE @Text XML = '<keys><key>1</key><key>2</key></keys>'
EXEC sp_PassXml @Text
Sample output of a simple query.
Key
-----------
1
2
2. Pass a table-valued parameter into the stored procedure
First, you have to define the user defined type for the table variable to be used by the stored procedure.
CREATE TYPE KeyTable AS TABLE ([Key] INT)
Then, you can use that type as a parameter for the stored proc (the READONLY is required since only IN is supported and the table cannot be changed)
CREATE PROC sp_PassTable
@Keys KeyTable READONLY
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM @Keys
END
GO
The stored proc can then be called with a table variable directly from SQL.
DECLARE @Keys KeyTable
INSERT @Keys VALUES (1), (2)
EXEC sp_PassTable @Keys
Note: If you are using .NET, then you can pass the SQL parameter from a DataTable type matching the user defined type.
Sample output from the query:
Key
-----------
1
2
answered Nov 28 '15 at 5:09
Jason WJason W
10.3k21644
2
While I have the accepted answer to the question, I am upvoting this because "just because the scope works".....doesn't mean its a good solution. Treating #tempTables like a global variable is bad routine practice. And I do exactly this, I pass xml all the time to promote set based operations to avoid cursors and RBAR.
– granadaCoder
Feb 15 '18 at 15:24
add a comment |
While understanding scoping addresses the direct need, thought it might be useful to add a few more options to the mix to elaborate on the suggestions from the comments.
- Pass XML into the stored procedure
- Pass a table-valued parameter into the stored procedure
1. Pass XML into the stored procedure
With XML passed into a parameter, you can use the XML directly in your SQL queries and join/apply to other tables:
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
Then a call to the stored procedure for testing:
DECLARE @Text XML = '<keys><key>1</key><key>2</key></keys>'
EXEC sp_PassXml @Text
Sample output of a simple query.
Key
-----------
1
2
2. Pass a table-valued parameter into the stored procedure
First, you have to define the user defined type for the table variable to be used by the stored procedure.
CREATE TYPE KeyTable AS TABLE ([Key] INT)
Then, you can use that type as a parameter for the stored proc (the READONLY is required since only IN is supported and the table cannot be changed)
CREATE PROC sp_PassTable
@Keys KeyTable READONLY
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM @Keys
END
GO
The stored proc can then be called with a table variable directly from SQL.
DECLARE @Keys KeyTable
INSERT @Keys VALUES (1), (2)
EXEC sp_PassTable @Keys
Note: If you are using .NET, then you can pass the SQL parameter from a DataTable type matching the user defined type.
Sample output from the query:
Key
-----------
1
2
answered Nov 28 '15 at 5:09
Jason WJason W
10.3k21644
While understanding scoping addresses the direct need, thought it might be useful to add a few more options to the mix to elaborate on the suggestions from the comments.
- Pass XML into the stored procedure
- Pass a table-valued parameter into the stored procedure
1. Pass XML into the stored procedure
With XML passed into a parameter, you can use the XML directly in your SQL queries and join/apply to other tables:
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
Then a call to the stored procedure for testing:
DECLARE @Text XML = '<keys><key>1</key><key>2</key></keys>'
EXEC sp_PassXml @Text
Sample output of a simple query.
Key
-----------
1
2
2. Pass a table-valued parameter into the stored procedure
First, you have to define the user defined type for the table variable to be used by the stored procedure.
CREATE TYPE KeyTable AS TABLE ([Key] INT)
Then, you can use that type as a parameter for the stored proc (the READONLY is required since only IN is supported and the table cannot be changed)
CREATE PROC sp_PassTable
@Keys KeyTable READONLY
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM @Keys
END
GO
The stored proc can then be called with a table variable directly from SQL.
DECLARE @Keys KeyTable
INSERT @Keys VALUES (1), (2)
EXEC sp_PassTable @Keys
Note: If you are using .NET, then you can pass the SQL parameter from a DataTable type matching the user defined type.
Sample output from the query:
Key
-----------
1
2
answered Nov 28 '15 at 5:09
Jason WJason W
10.3k21644
answered Nov 28 '15 at 5:09
Jason WJason W
10.3k21644
answered Nov 28 '15 at 5:09
Jason WJason W
10.3k21644
answered Nov 28 '15 at 5:09
Jason WJason W
10.3k21644
10.3k21644
2
While I have the accepted answer to the question, I am upvoting this because "just because the scope works".....doesn't mean its a good solution. Treating #tempTables like a global variable is bad routine practice. And I do exactly this, I pass xml all the time to promote set based operations to avoid cursors and RBAR.
– granadaCoder
Feb 15 '18 at 15:24
add a comment |
2
While I have the accepted answer to the question, I am upvoting this because "just because the scope works".....doesn't mean its a good solution. Treating #tempTables like a global variable is bad routine practice. And I do exactly this, I pass xml all the time to promote set based operations to avoid cursors and RBAR.
– granadaCoder
Feb 15 '18 at 15:24
2
2
While I have the accepted answer to the question, I am upvoting this because "just because the scope works".....doesn't mean its a good solution. Treating #tempTables like a global variable is bad routine practice. And I do exactly this, I pass xml all the time to promote set based operations to avoid cursors and RBAR.
– granadaCoder
Feb 15 '18 at 15:24
While I have the accepted answer to the question, I am upvoting this because "just because the scope works".....doesn't mean its a good solution. Treating #tempTables like a global variable is bad routine practice. And I do exactly this, I pass xml all the time to promote set based operations to avoid cursors and RBAR.
– granadaCoder
Feb 15 '18 at 15:24
add a comment |
protected by Community♦ Jan 9 '16 at 20:15
Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?
Instead of passing a comma-separated list in the first place, have you thought about using table-valued parameters? Also have you read this article?
– Aaron Bertrand
Nov 20 '13 at 19:47
Or XML, as SQL Server offers support for XML data type...
– Mihai Bejenariu
Nov 20 '13 at 19:53