Solved

Set money variable

Posted on 2010-11-23
5
174 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
  • 3
5 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

740 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