How to get sp_executesql result into a variable?

by

If you have OUTPUT parameters you can do

DECLARE @retval int   
DECLARE @sSQL nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

DECLARE @tablename nvarchar(50)  
SELECT @tablename = N'products'  

SELECT @sSQL = N'SELECT @retvalOUT = MAX(ID) FROM ' + @tablename;  
SET @ParmDefinition = N'@retvalOUT int OUTPUT';

EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@retval OUTPUT;

SELECT @retval;

But if you don’t, and can not modify the SP:

-- Assuming that your SP return 1 value
create table #temptable (ID int null)
insert into #temptable exec mysp 'Value1', 'Value2'
select * from #temptable

Not pretty, but works.

More Related Contents:

  1. Using a cursor with dynamic SQL in a stored procedure
  2. How to calculate age in T-SQL with years, months, and days
  3. Levenshtein distance in T-SQL
  4. Convert multiple rows into one with comma as separator [duplicate]
  5. How can I use optional parameters in a T-SQL stored procedure?
  6. Select query to remove non-numeric characters
  7. T-SQL Decimal Division Accuracy
  8. Selecting a Record With MAX Value
  9. T-SQL Pivot? Possibility of creating table columns from row values
  10. TSQL divide by zero encountered despite no columns containing 0
  11. T-SQL loop over query results
  12. T-SQL Dynamic SQL and Temp Tables
  13. How to Round a Time in T-SQL
  14. Dynamic SQL results into temp table in SQL Stored procedure
  15. How to convert a “dd/mm/yyyy” string to datetime in SQL Server?
  16. Dynamic SQL to generate column names?
  17. t-sql get all dates between 2 dates [duplicate]
  18. Return a value if no rows are found in Microsoft tSQL
  19. How to pivot table with T-SQL?
  20. Using Aliases in Where Clause or an Alternative Option?
  21. Testing for inequality in T-SQL
  22. Can I use the MERGE statement in SQL Server 2005?
  23. T-SQL: How to use parameters in dynamic SQL?
  24. Replace a newline in TSQL
  25. T-SQL – function with default parameters
  26. How to use an Alias in a Calculation for Another Field
  27. Check if a varchar is a number (T-SQL)
  28. String.Format like functionality in T-SQL?
  29. How to parse string into date?
  30. Stored procedure hangs seemingly without explanation

Leave a Comment