How to pass table values parameter using dynamic query in SQL server 2008

Question

I am calling one function using dynamic SQL query which accepts TVP. Is there any way to pass table values parameter in dynamic sql. One thing I can do to achieve this is to pass comma separated string and then convert this comma separated string to table. But the problem is then I have to convert string - table for each item in my table (The function is called for all the rows) which can be avoided if I can pass table in dynamic sql.

Thanks Ashwani

Passing an XML document is an option to you?

Rubens Farias
– Rubens Farias

2011-02-04 00:04:32 +00:00
Commented Feb 4, 2011 at 0:04 — Rubens Farias
– Rubens Farias, Commented Feb 4, 2011 at 0:04

canon · Accepted Answer · 2014-12-08 20:36:25Z

1

Certainly. Where inputTable is your TVP type and @test is the variable:

exec sp_executesql
 N'select
    t.*
   from @test as t
   where t.ID = 2'
,N'@test inputTable readonly' -- note the readonly
,@test

edited Dec 8, 2014 at 20:36

answered Feb 19, 2011 at 4:00

canon

41.9k10 gold badges77 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to pass table values parameter using dynamic query in SQL server 2008

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related