Member Avatar for culebrin

Hi all,

I have a Stored Procedure without an output parameter but it returns one row always, this SP is called by another SP, so I need to store some of those columns in a variables of the first SP. How can I do that?

Please help.

P.D.: I have no access to the code of the first SP, and is a CLR, HEEELPPP!!!

  • 2 Contributors
  • 2 Replies
  • 194 Views
  • 9 Months Discussion Span
  • Latest Post Latest Post by culebrin

Dani AI

Generated

Good solution by — capturing the CLR procedure’s single-row resultset into a temporary table and then reading that table into variables is the standard, reliable pattern when the called procedure cannot be changed (this is exactly what INSERT ... EXEC is for). ’s confirmation shows it works in practice. (learn.microsoft.com)

A few practical tips to make the pattern robust:

  • Discover the resultset schema first (so the temp table columns and types match). Use the built-in metadata helper rather than guessing:

    EXEC sp_describe_first_result_set N'EXEC dbo.YourClrProc @Param = 1', NULL, 0;

    That returns column names and types to use when defining the temp table. (learn.microsoft.com)

  • Use a guarded temp-table create/drop to avoid collisions:

    IF OBJECT_ID('tempdb..#ProcOut') IS NOT NULL DROP TABLE #ProcOut;

  • When moving values into scalar variables, select a single deterministic row (use TOP (1) + ORDER BY) so an unexpected multi-row result won’t produce nondeterministic assignments:

    SELECT TOP (1) @col1 = Col1, @col2 = Col2
FROM #ProcOut
ORDER BY ImportantTimestamp DESC;

Common pitfalls and troubleshooting:

  • The temp table’s column count/order/types must match what the proc returns; mismatches cause errors or truncation. Verify with sp_describe_first_result_set before creating the temp table. (stackoverflow.com)
  • If the CLR proc returns multiple resultsets (or uses INSERT...EXEC internally), the simple INSERT...EXEC approach won’t capture each resultset cleanly and nested INSERT...EXEC is blocked; those cases need a different approach (SQLCLR wrapper, client-side reader, or changing the proc). (dba.stackexchange.com)

If modifying the CLR proc is possible later, exposing the needed values as output parameters or a TVF is cleaner; otherwise the temp-table + INSERT...EXEC pattern is the pragmatic, widely used solution.

Recommended Answers

All 2 Replies

Member Avatar for huangzhi

Hope this code can help you.

1. Execute your first SP in query analyzer to show the columns list.

2. Create temporary table and the field must the same as first SP columns

create table #tmpTemp (
  Field1 varchar(255),
  Field2 varchar(255))

3. Insert the columns from first SP into temp table.

insert #tmpTemp exec sp_SP1

4. Store the column value.

declare @Field1 varchar(255)
select @Field1 = Field1 from #tmpTemp
Member Avatar for culebrin

Hope this code can help you.

1. Execute your first SP in query analyzer to show the columns list.

2. Create temporary table and the field must the same as first SP columns

create table #tmpTemp (
  Field1 varchar(255),
  Field2 varchar(255))

3. Insert the columns from first SP into temp table.

insert #tmpTemp exec sp_SP1

4. Store the column value.

declare @Field1 varchar(255)
select @Field1 = Field1 from #tmpTemp

That do the trick! thanks man!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.