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?
A TEMP Table of User Defined Table Type has to be created of the same schema as that of the Table Valued parameter and then it is passed as Parameter to the Stored Procedure in SQL Server.
The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table."
Global Temporary Tables. Stored procedures can reference temporary tables that are created during the current session. Within a stored procedure, you cannot create a temporary table, drop it, and then create a new temporary table with the same name.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With