How have strings within a string variable?

This works fine:

SELECT *
FROM MyTable
WHERE Code IN ('X', 'Y', 'Z')

But I want those values in a variable. If I do the below, it doesn't work because the X, Y, and Z aren't their own string:

DECLARE @.MyString varchar(50)
SET @.MyString = 'X, Y, Z'

SELECT *
FROM MyTable
WHERE Code IN (@.MyString)

Any way I can do what I'm trying to do?

Thanks for any help,

Ron

You couldn't use variables with IN statement. See this quote:

test_expression [ NOT ] IN ( subquery | expression [ ,...n ] )

expression[ ,... n ]

Is a list of expressions to test for a match. All expressions must be of the same type as test_expression.

Try to use dynamic SQL:

DECLARE @.MyString varchar(50)

SET @.MyString = '''X'', ''Y'', ''Z''' --You also need use quotes for each case

DECLARE @.MyQuery varchar(200)

SET @.MyQuery = '

SELECT *

FROM #MyTable

WHERE Code IN ('+@.MyString+')'

EXECUTE (@.MyQuery)

|||

Another method of breaking your strings into pieces is to use Jens Suessmeyer's SPLIT function. The advantage of this method is that it avoids potential SQL injection problems. An example of the split function can be found here:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17

Here are a couple of other examples of how the split function might be applied:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1314989&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1337001&SiteID=1

Still another way to do this would be to store your list as a blocked list of strings like in this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1418032&SiteID=1

|||

An alternative approach might be to do it like this:

declare @.aList table (n int identity)
insert into @.aList default values
insert into @.aList default values
insert into @.aList default values

declare @.myTable table
( rid int,
target varchar(2)
)
insert into @.myTable
select 1, 'A' union all
select 2, 'B' union all
select 3, 'X' union all
select 4, 'Z'

declare @.myString varchar(20)
set @.myString = 'X Y Z '

select a.*
from @.myTable a
inner join @.aList b
on target = rtrim(substring(@.myString, 2*n-1, 2))

-- rid target
-- --
-- 3 X
-- 4 Z

How format COMPUTE results?

Is it possible to format ARBalance below with commas?
SELECT ARBalance
FROM Invoice
compute SUM(ARBalance)
I have tried inserting variations from below with no luck:
CONVERT(varchar, CAST(SUM(Invoice.ARBalance) AS money), 1)
Thanks!Why don't you apply string formatting at the client/presentation tier? VB
has Format(), VBScript/ASP have FormatNumber() and FormatCurrency(), etc.
A
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:eelASapnFHA.3036@.TK2MSFTNGP14.phx.gbl...
> Is it possible to format ARBalance below with commas?
> SELECT ARBalance
> FROM Invoice
> compute SUM(ARBalance)
> I have tried inserting variations from below with no luck:
> CONVERT(varchar, CAST(SUM(Invoice.ARBalance) AS money), 1)
> Thanks!
>|||Hi
Formatting is really a function of the client, for instance if you are using
Query Analyser the "Use regional settings when displaying currency, number,
dates and times" check box on the connection tab does this.
John
"Mike Harbinger" wrote:

> Is it possible to format ARBalance below with commas?
> SELECT ARBalance
> FROM Invoice
> compute SUM(ARBalance)
> I have tried inserting variations from below with no luck:
> CONVERT(varchar, CAST(SUM(Invoice.ARBalance) AS money), 1)
> Thanks!
>
>|||Noye that COMPUTE is a deprecate feature. Use CUBE, ROLLUP or make the SUM
part of your query.
David Portas
SQL Server MVP
--|||Now how did I miss that! Thanks to all
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:03BC582C-EEA5-4AD2-BC33-B1CE1ED1A5AD@.microsoft.com...
> Hi
> Formatting is really a function of the client, for instance if you are
> using
> Query Analyser the "Use regional settings when displaying currency,
> number,
> dates and times" check box on the connection tab does this.
> John
> "Mike Harbinger" wrote:
>