CODEWITHSUNDEEP
sql-serverstored-proceduresparameters
webworm
webworm

Reputation: 11019

Can Table value parameters (TVP) be created inside and used in a single stored procedure?

I have a stored procedure that needs to accept multiple values in a stored procedure. After looking for ways to do this it looks like Table Value Parameters (TVP) are recommended in SQL Server 2008+. I am using SQL Server 2014.

What I am wondering is if TVPs can be created and used in the same stored procedure, or do the TVPs need to be created outside the stored procedure that they are used in? I would like to be able to enclose everything inside a single stored procedure if possible, so I could call something like this from code.

exec spMyProcedure "bob, Sam, Phil, Carol"

Ideally I would like to deploy the script via Code First in the entity framework seeding.

Upvotes: 0

Views: 832

Answers (1)

Hogan
Hogan

Reputation: 70523

Table Value Parameters are just Table Value Variables passed as a parameter.

So yes -- you can declare a Table Value Variable in your SP and use it in the same way you would use a Table Value Parameter.

Just like you can pass a string as a parameter or declare a string as a variable you can do the same thing with Table variables.

HOWEVER

Your example does not make sense. Here you are passing a string to a stored procedure. If that procedure is going to make that string into a table you should pass a TVP instead. That is the whole point of saying they are "recommended". What is recommended is using them instead of passing a string.

Upvotes: 1

Related Questions