Executing a table function from a stored procedure with multiple table parameters passed in?

Question

I have a stored procedure that executes some repeating code, so I decided to make redundant code a table function. The problem I am facing:

Msg 137, Level 16, State 1, Procedure Search, Line 98
Must declare the scalar variable "@myTVP".

A simple example of the SQL code that I use is:

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

If anyone can give any insight, that would be great!

(If you try to run this example, there are a few additional errors, but they come from the included error. The problem is that the search for the stored procedure cannot be created.

Thank.

Answer 1

. ( Go )

, ?

CREATE TYPE [dbo].textTable_type AS TABLE(
[text] [nvarchar] (36)
)
GO

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 [dbo].Search_fn
DROP PROCEDURE [dbo].Search
DROP TYPE [dbo].textTable_type