0

I'm in the process of updating some procs made for SQL 2008R2 to 2016 that are using a dynamic result set.

Assuming there is a procedure that always has exactly one result set, but the result set has a dynamic column return each time, is there a way to capture the column names at runtime in tsql without doing something janky? I'm not worried about data types, just column names.

Thanks!

1 Answer 1

1

There's no TSQL api to examine the resultset shape of a procedure that returns multiple different resultset shapes. For normal procs you can use sp_describe_first_result_set.

If you can afford to actually run the procedure and examine the results, the least-janky solution is to use SQL CLR. In CLR you get a SqlDataReader that you can use to examine the resultset shape.

2
  • Bummer. I had a feeling... I suppose I'll have to fire up Visual Studio...
    – Jsjjharp
    Nov 21, 2019 at 16:35
  • Yep. Stored procedures that return variable resultsets are always going to be a hassle. Nov 21, 2019 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.