How to debug a stored proc that accepts table-valued parameters

• gcresse

  SSCrazy

  Points: 2286

  More actions

  March 7, 2011 at 6:47 pm

  #247758

  We recently transitioned to SQL Server 2008 and I'm trying to take advantage of the table-valued parameters. My problem is, I'm not sure how to manually supply a table-valued parameter when I'm trying to debug--step into--the stored procedure from Management Studio or VS 2010. Hopefully there's a simple solution.

  Thanks.

• Sean Lange

  SSC Guru

  Points: 286609

  More actions

  March 8, 2011 at 7:43 am

  #1295872

  You can just declare a variable of your type and then run some inserts to populate it.

  declare @myTable myTableType

  insert @myTable

  select...[whatever data you need to make your type]

  select * from @myTable

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• gcresse

  SSCrazy

  Points: 2286

  More actions

  March 8, 2011 at 9:29 am

  #1295966

  Thanks. I guess I was making it more complicated that it needed to be.