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?
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> 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..
There's different ways to do it. But avoiding dynamic sql is safer. Especially if your @list is a user supplied..
ASKER
This works...thanks
I'll investigate the link provided for research
I'll investigate the link provided for research
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 .. ;-)
https://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_AvailableFo
WHERE afsid in (SELECT VALUE FROM dbo.ParmsToList(@list,',')
GROUP BY Left(afsSource,7)