Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Set money variable

Posted on 2010-11-23
5
Medium Priority
?
178 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 2000 total points
ID: 34198968
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
 
LVL 52

Expert Comment

by:_agx_
ID: 34198970
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
 
LVL 52

Expert Comment

by:_agx_
ID: 34198999
>> 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
 

Author Closing Comment

by:lrbrister
ID: 34199314
This works...thanks

I'll investigate the link provided for research
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34199851
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question