How to pass a temp table as a parameter into a separate stored procedure

by

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.

  1. Pass XML into the stored procedure
  2. 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

More Related Contents:

  1. Insert results of a stored procedure into a temporary table
  2. How to SELECT FROM stored procedure
  3. Function vs. Stored Procedure in SQL Server
  4. SQL Server Insert if not exists
  5. SQL Server – How to lock a table until a stored procedure finishes
  6. Stored procedure or function expects parameter which is not supplied
  7. What is a stored procedure?
  8. Search text in stored procedure in SQL Server
  9. SQL Call Stored Procedure for each Row without using a cursor
  10. SQL Server SELECT into existing table
  11. How do I execute a stored procedure once for each row returned by query?
  12. How to check if a stored procedure exists before creating it
  13. SQL: Select dynamic column name based on variable
  14. SQL : in clause in stored procedure:how to pass values
  15. How to return the output of stored procedure into a variable in sql server
  16. Split values over multiple rows [duplicate]
  17. How do I pass a list as a parameter in a stored procedure?
  18. Must declare the scalar variable
  19. Why execute stored procedures is faster than SQL query from a script?
  20. Custom Date/Time formatting in SQL Server
  21. Could not find stored procedure ‘dbo.aspnet_CheckSchemaVersion’
  22. Insert Update stored proc on SQL Server
  23. SQL Server: should I use information_schema tables over sys tables?
  24. Inserting rows into a table with one IDENTITY column only [duplicate]
  25. UNION the results of multiple stored procedures
  26. Eliminate and reduce overlapping date ranges
  27. Functions vs Stored Procedures
  28. Dynamic order direction
  29. How do I execute a MS SQL Server stored procedure in java/jsp, returning table data?
  30. Call stored procedure with table-valued parameter from java

Leave a Comment