Set money variable

My attached code works and returns a single value.

I need to be able to store that result in a @variable for use later.

How do I do that?
Declare @list NVarchar(30)
Set @list = '190,191,192,193'

Declare @sql nvarchar(1000)
Set @sql='
select Sum(afsAmount) from proc_cfa.dbo.P_AvailableForSale where afsid in (' + @list + ') group by Left(afsSource,7)'
exec (@sql)

Open in new window

Larry Bristersr. DeveloperAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
Declare @list NVarchar(30)
Set @list = '190,191,192,193'

declare @m money
Declare @sql nvarchar(1000)
Set @sql='
select @m = Sum(afsAmount) from proc_cfa.dbo.P_AvailableForSale where afsid in (' + @list + ') group by Left(afsSource,7)'
exec sp_executesql @sql, N'@m money output', @m output
select @m
0
 
_agx_Commented:
Personally, I would get rid of the dynamic sql. Then it would be easy. This article has some great ways to converting @lists to something you can use in a non-dynamical-sql query

http://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html

ie
DECLARE @YourVariable ...

SELECT @YourVariable = SUM(afsAmount)
FROM    proc_cfa.dbo.P_AvailableForSale
WHERE afsid in (SELECT VALUE FROM dbo.ParmsToList(@list,',') );
GROUP BY Left(afsSource,7)

0
 
_agx_Commented:
>> Personally, I would get rid of the dynamic sql.

There's different ways to do it. But avoiding dynamic sql is safer. Especially if your @list is a user supplied..
0
 
Larry Bristersr. DeveloperAuthor Commented:
This works...thanks

I'll investigate the link provided for research
0
 
_agx_Commented:
Yeah, it's a good read and the function's a good way to avoid sql injection. If you're interacting with user data that is .. ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.