title: Executing a Table-Valued Function from a Stored Procedure with multiple Table-Valued Parameters being passed through?
body: <p>I've got a stored procedure that executes some repetitive code, so I decided to make the redundant code into a table-valued function. The problem that I'm encountering is:</p>
  
  <pre><code>Msg 137, Level 16, State 1, Procedure Search, Line 98
  Must declare the scalar variable "@myTVP".
  </code></pre>
  
  <p>A simple example of the SQL code that I'm using is:</p>
  
  <pre><code>CREATE TYPE [dbo].textTable_type AS TABLE(
  [text] [nvarchar] (36)
  )
  
  CREATE FUNCTION dbo.Search_fn
  (@myTVP AS textTable_type READONLY)
  RETURNS TABLE
  AS
  RETURN
  (   SELECT * from @myTVP    )
  GO
  
  CREATE PROCEDURE [dbo].[Search]
  @myTVP AS textTable_type READONLY
  AS
  BEGIN
  
  SELECT * FROM dbo.Search_fn(@myTVP)
  
  END
  GO
  
  DECLARE @TVP as textTable_type
  INSERT INTO @TVP VALUES ('abc')
  INSERT INTO @TVP VALUES ('123')
  exec dbo.Search(@myTVP = @TVP)
  GO
  
  DROP FUNCTION Search_fn
  DROP PROCEDURE Search
  </code></pre>
  
  <p>If anyone can provide any insight, that would be wonderful!</p>
  
  <p>(There are a couple extra errors if you try to run this example, but they originate from the error included. The problem is because the stored procedure Search cannot be created.</p>
  
  <p>Thank you.</p>