Is there a way to extract a value/result when you exec a sql string

littleGreenDude

SQL Server: Is there a way to extract a value/result when you exec a sql string? DECLARE @PaperCost money declare @PaperCategories nvarchar(30) set @PaperCategories = '3,7' DECLARE @sqlcmd varchar(255) select @sqlcmd = ' SELECT sum(amounts) FROM tbl_CostInfo where CatID in (' + RTRIM(@PaperCategories) + ') ' **SELECT @PaperCost = exec( @sqlcmd )** This sql block will reside inside of a stored proc, and the idea is to have a string passed in for the category values ('3,7' in this example) and return the corresponding cost. However, sql doesn't like the last line. Is there a way to extract the resulting value from an exec command?

Christian Graus

Why not pass the categories as XML, use OpenXML to turn them into data, and do a normal query ?

Christian Graus - Microsoft MVP - C++ "also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )

andyharman

You could try:

create table #temp1 (
  SumAmounts money
  )
set @SQL = 'SELECT sum(amounts) FROM tbl_CostInfo .....'
exec(@SQL)
select @PaperCost = SumAmounts from #temp1
drop table #temp1

However, a better way of acheiving this would be to use a UDF to turn your @PaperCategories string into a list of values. The resulting SQL would look a bit like:

select @PaperCost = SUM(Amounts) from tbl_CostInfo
  where CatId in (
    select Element from dbo.udf_split(@PaperCategories))

You should be able to find the source code for a similar UDF using google. Regards Andy