comma delimited string in a local variable.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

comma delimited string in a local variable.

Author

Topic

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-10-04 : 13:14:44

DECLARE @FundList varchar(100)
SET @FundList = ''
SELECT @FundList = @FundList + ', ' + ''''+ CAST(FS.Name AS varchar(200))+ ''''
FROM POFund F INNER JOIN FundDetail FD ON F.FundDetailId = FD.FundDetailId
INNER JOIN FundSource FS ON FS.FundNumber = FD.FundNumber
WHERE F.PurchaseOrderId = 1
GROUP BY F.PurchaseOrderId,FS.Name,FS.FundNumber

SELECT @FundList = SUBSTRING(@FundList, 3, 100)
SELECT @FundList

The above SQL will return the string 'a', 'b', 'c'

The following SQL doesn't return any records.
SELECT FundNumber, FundName,FY1, FY2, FY3, FY4, FY5, FY6, FY7, FY8, FY9, FY10 FROM view_POAmendmentFund
WHERE FundName IN (Select @FundList)

I know there are some records returned from view_POAmendmentFund for 'a'.

But If I do these, I do get the matching records for 'a'.
SELECT FundNumber, FundName,FY1, FY2, FY3, FY4, FY5, FY6, FY7, FY8, FY9, FY10 FROM view_POAmendmentFund
WHERE FundName IN ('a', 'b', 'c')

Why is my "select @FundList" not working in the WHERE clause?

Thanks a ton for your help. This website is my life saver.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-04 : 13:45:26

Try like below

DECLARE @FundList varchar(100)
SET @FundList = ''
SELECT @FundList = @FundList + ', ' +  CAST(FS.Name AS varchar(200))+ ','
FROM POFund F INNER JOIN FundDetail FD ON F.FundDetailId = FD.FundDetailId
INNER JOIN FundSource FS ON FS.FundNumber = FD.FundNumber
WHERE F.PurchaseOrderId = 1
GROUP BY F.PurchaseOrderId,FS.Name,FS.FundNumber


SELECT FundNumber, FundName,FY1, FY2, FY3, FY4, FY5, FY6, FY7, FY8, FY9, FY10 FROM view_POAmendmentFund
WHERE @FundList LIKE '%,'+FundName+',%'

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-10-04 : 13:54:50

visakh16, did you mean to say....

SELECT FundNumber, FundName,FY1, FY2, FY3, FY4, FY5, FY6, FY7, FY8, FY9, FY10 FROM view_POAmendmentFund
WHERE FundName LIKE '%,'+@FundList+',%'

If so, this is returning any records either.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-04 : 13:57:28

quote:
Originally posted by rum23

visakh16, did you mean to say....

SELECT FundNumber, FundName,FY1, FY2, FY3, FY4, FY5, FY6, FY7, FY8, FY9, FY10 FROM view_POAmendmentFund
WHERE FundName LIKE '%,'+@FundList+',%'

If so, this is returning any records either.

nope just opposite. Use code posted by me. Try copying and run it

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-10-04 : 14:02:14

hmmmm....its not working

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-04 : 14:10:20

how are values in FundName existing? Also are you sure you used my posted query. I've changes some part of your initial query as well.

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-10-05 : 10:14:52

Fund Names are some typical text values like test1 , name3, etc. Yes, i did use the query that you posted.

SELECT FundNumber, FundName,FY1, FY2, FY3, FY4, FY5, FY6, FY7, FY8, FY9, FY10 FROM view_POAmendmentFund
WHERE FundName LIKE '%,'+@FundList+',%'

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 13:20:03

quote:
Originally posted by rum23

Fund Names are some typical text values like test1 , name3, etc. Yes, i did use the query that you posted.

SELECT FundNumber, FundName,FY1, FY2, FY3, FY4, FY5, FY6, FY7, FY8, FY9, FY10 FROM view_POAmendmentFund
WHERE FundName LIKE '%,'+@FundList+',%'

Thanks!

this was not query i posted. have a look at post made on 10/04/2008 : 13:45:26. its

SELECT FundNumber, FundName,FY1, FY2, FY3, FY4, FY5, FY6, FY7, FY8, FY9, FY10 FROM view_POAmendmentFund
WHERE @FundList  LIKE '%,'+FundName+',%'

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-10-06 : 13:29:15

I got it working by using the following sql. Thanks!

DECLARE @FundList varchar(100)
SET @FundList = ''
SELECT @FundList = @FundList + CAST(FS.Name AS varchar(200))+ ','
FROM POFund F INNER JOIN FundDetail FD ON F.FundDetailId = FD.FundDetailId
INNER JOIN FundSource FS ON FS.FundNumber = FD.FundNumber
WHERE F.PurchaseOrderId = 13
GROUP BY F.PurchaseOrderId,FS.Name,FS.FundNumber

select @FundList
SELECT FundNumber, FundName,FY1, FY2, FY3, FY4, FY5, FY6, FY7, FY8, FY9, FY10 FROM view_POAmendmentFund
WHERE @FundList LIKE '%,'+FundName+',%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 13:37:51

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources